Ranking Data in Power BI DAX

[Cevola]

Hi,

I am trying to Rank data in Power BI. Pictures is a meature. Now i want to create a measure to rank Pictures. The location with the highest pictures should be 1 and so on.

This is the measure i created but it gives me only 2 values 1 and 889419. 1 for all having some value and 0 for nulls.

Measure = rankx(ALLSELECTED('Table'),'Table'[Pictures],,DESC,Skip)

Please help me.

[KNP · Accepted solution]

Actually, it could be that you're referencing the table without the column in the ALLSELECTED.

Try this...

_Measure =
RANKX (
    ALLSELECTED ( 'Table'[Location] ),
    'Table'[Pictures],
    ,
    DESC,
    SKIP
)

[Cevola]

Thank You!

[KNP]

Hi @Cevola,

Just a couple of things first.

You've posted a DAX question in the Power Query forum. You'll probably get an answer quicker if posted in the DAX forum.

Please paste data next time, not a screen shot. It makes it faster to come up with a solution.

As for the solution, I've thrown together a very simple example, attached.

The code should be working fine which leads me to believe the issue is with your model.

Can you post a screen shot of your model and the relationships?

Thanks

[KNP · Accepted solution]

Actually, it could be that you're referencing the table without the column in the ALLSELECTED.

Try this...

_Measure =
RANKX (
    ALLSELECTED ( 'Table'[Location] ),
    'Table'[Pictures],
    ,
    DESC,
    SKIP
)

[Cevola]

Thank You, your measure worked like magic.

Now, I need to create one more measure to Rank the pictures 

Rank of: 

Pictures  –  

top 20 = 1 

Top 50 = 0.7 

Top 100 = 0.5 

Top 200 = 0.3 

Can you please help me in this?

Thanks a lot in advance

Top 200 = 0.3 

[KNP]

If I understand correctly, this measure should work.

(change [Measure] to whatever you've called it)

RankPictures = 
    SWITCH(
        TRUE(),
        [Measure] < 21, 1,
        [Measure] < 51, 0.7,
        [Measure] < 101, 0.5,
        [Measure] < 201, 0.3,
        0
    )

Please mark all the responses that solved your issues as the solution.

[KNP · Accepted solution]

If I understand correctly, this measure should work.

(change [Measure] to whatever you've called it)

RankPictures = 
    SWITCH(
        TRUE(),
        [Measure] < 21, 1,
        [Measure] < 51, 0.7,
        [Measure] < 101, 0.5,
        [Measure] < 201, 0.3,
        0
    )

Please mark all the responses that solved your issues as the solution.