Forum Discussion

Cevola's avatar
Cevola
Helper I
3 years ago
Solved

Ranking Data in Power BI DAX

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.

 

 

 

Need Help
  • KNP's avatar
    KNP
    3 years ago

    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
)
  • KNP's avatar
    KNP
    3 years ago

    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.

     

6 Replies

  • KNP's avatar
    KNP
    Super User
    3 years ago

    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's avatar
      KNP
      Super User
      3 years ago

      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's avatar
        Cevola
        Helper I
        3 years ago

        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