Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
and i need
measure1 = C
measure2 = 6
where rank is 3; related values for it
Thanks,
Cosmin
Solved! Go to 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.
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.
perfect!! this i didn't know
MAXX(FILTER(_table2,[rank]=SELECTEDVALUE(Parameter[Parameter])),[res])
Thanks a lot!
Cosmin
User | Count |
---|---|
77 | |
75 | |
46 | |
31 | |
28 |
User | Count |
---|---|
99 | |
91 | |
51 | |
49 | |
46 |