Forum Discussion

carlochecchia's avatar
carlochecchia
Advocate I
5 years ago
Solved

Dynamic Ranking Measure

Hi, I was wondering if I could get some help in order to set up my report. I have data for 6 Twitter WebPages. The dataset contains information on the WebPage, Type, and Followers  as per table be...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi carlochecchia 

     

    You can use RANKX function to create a calculated column. This would be easy.

    Rank Column = 
RANKX (
    FILTER (
        'Twitter',
        'Twitter'[Type] = EARLIER ( 'Twitter'[Type] )
            && Twitter[Data] = EARLIER ( Twitter[Data] )
    ),
    Twitter[Followers],
    , // leave value argument blank
    DESC
)

     

     

    Then create a measure to get the rank column value. 

    Ranking Measure = SELECTEDVALUE(Twitter[Rank Column])

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

v-jingzhang's avatar
v-jingzhang
Community Support
5 years ago

Hi carlochecchia 

 

I have one question that hope you can make it clearer.

 

When you have more than one record per webpage on the same day, just like ABC-MEDIA, which record of followers is the latest record we should take into account? In the Data column, there is only date value without exact time value, so I cannot tell which one is the latest. Maybe the largest followers record is the one you need? Is there any possibility that the latest record has a smaller value? Or maybe you can add exact time values of all records?

carlochecchia's avatar
carlochecchia
Advocate I
5 years ago

v-jingzhang once again thank you for getting back to me with this matter. The original data has the date and timestamp in it. Im sorry i didnt include it in the sample file I sent you. ( I have updated it and included the column DateTime. The link remains the same.
I would like to use the latest available data for the end of the day.
Best Regards

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    Hi carlochecchia 

     

    I create a new table in Power Query Editor based on original table to fill in blank values on dates which have no values and remove non-latest rows on dates which have multiple values. The result is as below. You can then use this new table in the report to get the rank value. I keep the original table in the model too. 

     

    I believe there is DAX method to get what you want, but I don't know how to do that. ğŸ˜¬

     

    Jing