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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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