DAX value in a measure from a virtual summarized table with rank

[cosminc]

Hi all,

i'm struggling with a chalenge to obtain measures with correspondent values of a column where rank is n; the table is virtual, a summarized one in dax with rank added.

initial_table:

Attr	result
A	1
A	2
B	3
B	3
B	3
C	3
C	3
D	10

summarized virtual table is like this:

Attr	result	_rank_desc
A	3	4
B	9	2
C	6	3
D	10	1

VAR _summarized_table = SUMMARIZE('Table', 'Table'[Attr], "result", CALCULATE(SUM('Table'[result])))

VAR _rank_from__summarized_table = ADDCOLUMNS(_summarized_table, "_rank_desc", RANKX(_summarized_table, [result],,DESC, Dense))

RETURN

_rank_from__summarized_table

and i need 

measure1 = C

measure2 = 6

where rank is 3; related values for it

Thanks,

Cosmin

[Anonymous · Accepted solution]

Hi @cosminc ,

You can create a what-if parameter to provide the dynamic rank n.

Use what-if parameters to visualize variables - Power BI | Microsoft Learn

Then create the following two measure.

Measure1 = var _table=SUMMARIZE('Table','Table'[Attr],"res",SUM('Table'[result]))
var _table2=  ADDCOLUMNS(_table,"rank",RANKX(_table,[res],,DESC,Dense))
return MAXX(FILTER(_table2,[rank]=SELECTEDVALUE(Parameter[Parameter])),[Attr])

Measure2 = var _table=SUMMARIZE('Table','Table'[Attr],"res",SUM('Table'[result]))
var _table2=  ADDCOLUMNS(_table,"rank",RANKX(_table,[res],,DESC,Dense))
return MAXX(FILTER(_table2,[rank]=SELECTEDVALUE(Parameter[Parameter])),[res])

You can download my attachment for details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cosminc]

perfect!! this i didn't know

MAXX(FILTER(_table2,[rank]=SELECTEDVALUE(Parameter[Parameter])),[res])

Thanks a lot!
Cosmin