Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cosminc
Post Partisan
Post Partisan

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

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:

 

Attrresult
A1
A2
B3
B3
B3
C3
C3
D10

 

 

summarized virtual table is like this:

 

Attrresult_rank_desc
A34
B92
C63
D101

 

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

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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

vstephenmsft_0-1666864801624.png

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.

 

 

View solution in original post

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

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

vstephenmsft_0-1666864801624.png

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.

 

 

perfect!! this i didn't know

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

Thanks a lot!
Cosmin

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.