Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Everyone!
Need your help on Power BI Rank Dax function
We have the data like below
Table Visual
Brand | Product | Sales | Rank |
A | C | 200 | 1 |
A | D | 190 | 2 |
A | E | 180 | 3 |
A | A | 170 | 4 |
A | F | 160 | 5 |
B | Z | 900 | 1 |
B | I | 800 | 2 |
B | Q | 700 | 3 |
B | N | 400 | 4 |
B | B | 300 | 5 |
B | S | 200 | 6 |
C | A | 900 | 1 |
C | B | 300 | 2 |
C | Q | 150 | 3 |
C | R | 100 | 4 |
C | C | 90 | 5 |
C | G | 80 | 6 |
sales and rank are measure
sales is a dynamic measure , the value of the sales will change based on Time Period (YTD,MTD,QTR)
Sales =
var SelectedMeasure =
SELECTEDVALUE('Time Period'[Time Period],"YTD")
var DynamicMeasureValue =
SWITCH(
TRUE(),
SelectedMeasure = "YTD", [YTD],// gives you the YTD sales
SelectedMeasure = "QTD", [QTD],// gives you the QTD sales
SelectedMeasure = "MTD", [MTD],// gives you the MTD sales
BLANK()
)
RETURN
DynamicMeasureValue
Time Period is a disconnected TABLE. PFB
Time Period | Time Key |
MTD | 1 |
QTD | 2 |
YTD | 3 |
created a slicer out of this table , which will give sales based on the slicer selection(YTD,QTD,MTD)
Rank = RANKX(
ALL('TABLE'[Product]),
[Sales]
)
I Just need to apply a filter where Product = Brand
want the output like below
Brand | Product | Sales | Rank |
A | A | 170 | 4 |
B | B | 300 | 5 |
C | C | 90 | 5 |
I have created a flag calclated column to achieve that
flag = IF(
'TABLE'[Brand]='TABLE'[Product],
1,
0
)
I have added this as a visual level filter and selected 1
getting the output like this
Brand | Product | Sales | Rank |
A | A | 170 | 1 |
B | B | 300 | 1 |
C | C | 90 | 1 |
Please help how can we get the desired output like below
Brand | Product | Sales | Rank |
A | A | 170 | 4 |
B | B | 300 | 5 |
C | C | 90 | 5 |
Thanks,
Ananth
Solved! Go to Solution.
Hi @Anonymous
Please change the flag Calculate column to a Measure. Then, the result will look like this.
Best Regards,
Community Support Team _ Caiyun
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Hi @Anonymous
Please change the flag Calculate column to a Measure. Then, the result will look like this.
Best Regards,
Community Support Team _ Caiyun
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
HI !,
Thanks for your response.
Actually sales is a dynamic measure , the value of the sales will change based on Time Period (YTD,MTD,QTR)
Sales =
var SelectedMeasure =
SELECTEDVALUE('Time Period'[Time Period],"YTD")
var DynamicMeasureValue =
SWITCH(
TRUE(),
SelectedMeasure = "YTD", [YTD],// gives you the YTD sales
SelectedMeasure = "QTR", [QTD],// gives you the QTD sales
SelectedMeasure = "MTD", [MTD],// gives you the MTD sales
BLANK()
)
RETURN
DynamicMeasureValue
Time Period is a disconnected TABLE. PFB
Time Period | Time Key |
MTD | 1 |
QTD | 2 |
YTD | 3 |
created a slicer out of this table , which will give sales based on the slicer selection(YTD,QTD,MTD)
Thanks,
Ananth
I've recreated your data in a test file on my end. The only thing that I've done that you didn't explicitly say you did was create a measure for sales:
$ Sales = SUM(SampleData[Sales])
The rank measure is:
Rank =
RANKX(
ALL(SampleData[Product]),
SampleData[$ Sales]
)
and a calculated column for Product = Brand:
ProductBrandMatchFlag =
IF(
SampleData[Product] = SampleData[Brand],
1,
0
)
I created a table visualization and dropped the ProductBrandMatchFlag filter onto it. When I filter by ProductBrandMatchFlag = 1 I get your desired result:
User | Count |
---|---|
48 | |
44 | |
19 | |
14 | |
14 |
User | Count |
---|---|
107 | |
57 | |
29 | |
20 | |
16 |