Forum Discussion
RANKX and visual level filter
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 |
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!🙂
4 Replies
- AlB
Community Champion
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
- littlemojopuppy
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!🙂
- amitchandak
Super User
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/367415- littlemojopuppy
@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???
