Return column name based on criteria

DAX DAX Calculations
1

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”

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

thanks in advance for your help, kind regards
Franz

samplefile.pbix (40.5 KB)

2

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.

image
image.png507×590 21.9 KB
[Switch Statement.pbix|attachment]

Switch Statement.pbix (44.8 KB)

3

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:
image

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]
    )
    )

image

2 Likes
4

Awesome solution here Nick

5

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

thanks a lot
Franz