Forum Discussion
AltGr9
Helper I
Helper IReturn Top Result in Matrix using DAX
Hi I have a table of data that looks like this:
And when I put it into a Matrix it looks like this:
And what I'm looking for is a Matrix that looks like this:
I've tried various things in the .pbix attached but none of them work.
I'd like to achieve it using DAX if possible, so that I can add to it in future. Can you help?
Thanks in advance.
Hi AltGr9
Add the Class_new and Row Header columns to the data tableClass_new = SWITCH( TRUE(), [Class] = "Class 1", "Class A", [Class] = "Class 2", "Class B", [Class] = "Class 3", "Class C") --------------------------------------- Row Header = "Top Site"
Follow these stepsТop Site = VAR _maxdate = CALCULATE ( MAX ( 'Data'[Amount] ), ALLEXCEPT ( 'Data', 'Data'[Class_new] ) ) RETURN CALCULATE ( MAX ( 'Data'[Site] ), FILTER ( 'Data', 'Data'[Class] = MAX ( 'Data'[Class] ) && 'Data'[Amount] = _maxdate ) )
2 Replies
DimaMDSolution Sage
Hi AltGr9
Add the Class_new and Row Header columns to the data tableClass_new = SWITCH( TRUE(), [Class] = "Class 1", "Class A", [Class] = "Class 2", "Class B", [Class] = "Class 3", "Class C") --------------------------------------- Row Header = "Top Site"
Follow these stepsТop Site = VAR _maxdate = CALCULATE ( MAX ( 'Data'[Amount] ), ALLEXCEPT ( 'Data', 'Data'[Class_new] ) ) RETURN CALCULATE ( MAX ( 'Data'[Site] ), FILTER ( 'Data', 'Data'[Class] = MAX ( 'Data'[Class] ) && 'Data'[Amount] = _maxdate ) )
