# Return column name based on criteria

Hello, i have a topic which seems to be quite simple, at least i hope so, I have two tables, please see samplefile.

table “items” contains: items, item-Groups and age of the item
table “table” contains item-group and classification-groups

so what i would like to achieve, preferrably in a calculated column, is to return the column Name, based on itemsgroup and age of days, if above very aged, then “spoiled”

e.g. "if beer is 48 days old, it is “aged”, beause it is above 30 and below 60

Franz

samplefile.pbix (40.5 KB)

Here is solution I came up with using a SWITCH statement. Please review Sam’s material on this, very powerful DAX function. No need for a calculated column.

[Switch Statement.pbix|attachment]

Switch Statement.pbix (44.8 KB)

I think this can be done using static segmentation method. First thing is to get a table set up correctly. I just used the datatable construct for ease:

``````DATATABLE(
"itemgroup",string
,"Band",string
,"Min",integer
,"Max",integer
,
{
{"beer","fresh",0,30}
,{"beer","aged",30,60}
,{"beer","very aged",30,99999}

,{"milk","fresh",0,2}
,{"milk","aged",2,3}
,{"milk","very aged",3,99999}

,{"yogurt","fresh",0,5}
,{"yogurt","aged",5,10}
,{"yogurt","very aged",10,99999}
}
)
``````

So we get this table which I so cleverly named Range table:

Then we can write a measure to find the value that starts with the correct itemgroup, and where the age falls. Do not use implicit measures though, explicitly write a measure even if it’s just a simple sum

``````Measure =
CALCULATE(
VALUES( 'Range Table'[Band] ),
FILTER(
'Range Table',
[Sum of Age] >='Range Table'[Min]
&&
[Sum of Age] < 'Range Table'[Max]
&&
SELECTEDVALUE(items[itemgroup]) = 'Range Table'[itemgroup]
)
)
``````

2 Likes

Awesome solution here Nick

Hello nick, thanks for your help, seems to work perfectly, could you please be kind and upload the samplefile?

thanks a lot
Franz