CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Johnny Spindler
Johnny Spindler

Reputation: 456

How to RANK in Visual depending on Slicer

In my Power BI Report I have a table called "Answers" that looks like this:

|Animal  | Viewed |
|Cat     | 17     |
|Chicken | 12     |
|Cow     | 7      |
|Dog     | 28     |
|Goat    | 36     |

I have a slicer on Animal and I need to RANK the Animals by Views in the Visual. So I would need a Measure.
Expected Result:

|Animal  | Viewed | RANK |
|Chicken | 12     | 2    |
|Cow     | 7      | 3    |
|Dog     | 28     | 1    |



Fail 1. The RANK Dense Fuction however did not work:

Rank_fkt = RANKX(ALL(Answers),CALCULATE(MAX(Answers[Viewed])),,DESC,Dense)

|Animal  | Viewed | Rank_fkt |
|Chicken | 12     | 4        |
|Cow     | 7      | 5        |
|Dog     | 28     | 2        |

Fail 2. The RANK AllSELECTED Fuction however did not work either:

Rank_ALL = rankx(ALLSELECTED(Answers[Animal]),CALCULATE(MAX(Answers[Viewed])))

|Animal  | Viewed | Rank_ALL |
|Chicken | 12     | 1        |
|Cow     | 7      | 1        |
|Dog     | 28     | 1        |

Fail 3. My atempt with RANK and Selectedvalue did not work either:

RANK_sel = 
VAR SelectedAnimal = SELECTEDVALUE(Answers[Animal])
RETURN
    RANKX(
        ALL('Answers'),
        CALCULATE(SUM('Answers'[Viewed]), Answers[Animal] = SelectedAnimal),,DESC,Dense
         )

|Animal  | Viewed | Rank_fkt |
|Chicken | 12     | 1        |
|Cow     | 7      | 1        |
|Dog     | 28     | 1        |




Can please someone help me to find the measure that RANKs on the Visual context depending on the slicer, as shown in the Expoected Result.

Upvotes: 2

Views: 1017

Answers (2)

Luke_0
Luke_0

Reputation: 855

Rank = RANK(Dense, [table_name], ORDERBY([order_by_expression], Desc))

Dense can be substituted for skip to create a rank gap where there are ties (e.g., skip tied ranks rather than keeping ranks continuous).

For the [order_by_expression], you should be able to just use your Answers[Viewed] column directly. E.g.,

Rank = RANK(Dense, Answers, ORDERBY(Answers[Viewed], Desc))

https://learn.microsoft.com/en-us/dax/rank-function-dax

Rank is more efficient than RANKX when the the order-by expression and the expressions to be ranked are one and the same list (i.e., ranking a pre-existing list of values by itself like you have). RANKX is more useful when you are ranking a value not found in the order-by expression (e.g., rank 3.5 within the list of whole numbers {1...5}).

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

You can create a Measure as below-

Rank = 
CALCULATE(
    COUNTROWS(ALLSELECTED(your_table_name)),
    FILTER(
        ALLSELECTED(your_table_name),
        your_table_name[Viewed] >= MIN(your_table_name[Viewed])
    )
)

Here is the sample output with order applied on Rank column-

enter image description here

Upvotes: 1

Related Questions