How to retrive name of corresponding category of biggest value

[Pbiuserr]

Hello,

I have a measure like

Top Qty Category =

VAR _table =

ADDCOLUMNS(

VALUES(Fct_Table[Category]),

"@Chng", [Percentage change]

)

VAR _top_value =

MAXX(_table, [@Chng] )

RETURN

_top_value

I'd also like return corresponding category in the output, in this return. How to do that?

[rohit_singh]

Hi @Pbiuserr ,

Create a variable to calculate the category
var _top_category = <your code>

when you return the value you can use the concatenate function to display both values together
RETURN
Concatenate(_top_value, _top_category)

[Pbiuserr]

Can you help me with that measure? I think thats the part I struggle with

[rohit_singh]

It's hard to create a measure without sample data, but you can use the logic below :

var _top_category = 
CALCULATE(values(Fct_Table[Category]),
          FILTER(Fct_Table, Fct_Table[<col_name>] = _top_value)
)

Basically filter out your table where your column value is equal to the max value and return the corresponding category

[Pbiuserr]

I tried similarly but got an error of :

MdxScript(Model) (312, 34) Calculation error in measure 'Calculations'[Top Qty Category ]: DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

Basically I want to return max value along with coresponding name of column Fct_Table[Category] of a measure. Can be any, like SalesAmount

[Pbiuserr · Accepted solution]

Okay i got it by 

FIRSTNONBLANK(TOPN(1,VALUES(Fct_Table[Category]),[Percentage change]),1)