Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table containing locations [tLocation], another related table containing products [tProduct]. I want to be able to select a single location in a visual filter and see a rank (as a card) of unique product types for that location. New York has two unique product types, and should rank 1 if selected. If Los Angeles or Houston is selected, they have one unique type and should rank as 2. If Chicago is selected, no types and rank of 3. How can I build measures for distinctcount and rank where the count and rank will work whether a selection is made in a filter dropdown or if all are selected.
Location |
New York |
Los Angeles |
Chicago |
Houston |
Product | Type | Location |
A | Red | New York |
A | Red | Los Angeles |
A | Red | Houston |
B | Red | New York |
B | Red | Los Angeles |
B | Red | Houston |
C | Red | New York |
C | Red | Los Angeles |
D | Blue | New York |
Solved! Go to Solution.
Hi @akarasick1
Two measures...
Product Count = COUNTROWS(Products)
Product Ranking =
RANKX(
ALL(Locations),
[Product Count],
[Product Count],
DESC,
Skip
)
You should be able to drop the Product Ranking measure into a card. Hope this helps!🙂
@akarasick1 , Try Rank like a new measure
rankx(allselected(Table), calculate(DistinctCount(Table[Product])),,desc,dense)
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
@amitchandak I posted a response to this four hours ago with a screen shot showing that the solution I offered works. @AlB offered a solution before I did (I started working on this while there were no responses). Why did you feel that another solution was necessary???
Hi @akarasick1
Two measures...
Product Count = COUNTROWS(Products)
Product Ranking =
RANKX(
ALL(Locations),
[Product Count],
[Product Count],
DESC,
Skip
)
You should be able to drop the Product Ranking measure into a card. Hope this helps!🙂
Hi @akarasick1
Try this measure in a card visual:
Rank measure =
RANKX(ALL(tLocation[Location]), CALCULATE(DISTINCTCOUNT(tProduct[Type])),,DESC,Dense)
See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers