RANKX and visual level filter

[akarasick1]

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

[amitchandak]

@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...

[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???

[littlemojopuppy · Accepted 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!🙂

[AlB]

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