Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ccakjcrx
Resolver I
Resolver I

Rank Date

Hello!

 

I'm trying to brake my bad habit of adding columns to arrive at a desired outcome, and instead create measures when possible.

 

I believe this is a fairly simple problem, but I cannot solve it: I simply want a MEASURE to rank the dates in the following table visual (you can access the pbix and data file HERE😞

 

Table.jpg

 

 

 

 

 

 

 

 

 

 

 

I simply want to add a MEASURE that 'ranks' these in ascending order. I tried to use both COUNTROWS and RANKX, but could not produce the outcome I'm looking for. In the end, I want the measure to be added to the table visual, and populate a value to the right of each date that ranks each respective date. 

 

I think this logic is what I'm after: I want the measure to iterate through the list, and compare each row to the MIN date in the row, and COUNT that row--adding to the count each time that condition is TRUE. 

 

Thanks for the consideration.

1 ACCEPTED SOLUTION

Hi @ccakjcrx

 

Try this MEASURE

 

Index =
RANKX (
    ALLSELECTED ( Sheet1[Month Year] ),
    CALCULATE ( SELECTEDVALUE ( Sheet1[Month Year] ) ),
    ,
    ASC
)

Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
miltenburger
Helper V
Helper V

Hi @ccakjcrx

 

is this what you want?

 

thisor.png

@miltenburger

 

HEY!

 

Thanks for responding. I probably should have included this in my original post. 

 

This is the outcome I am looking for:

 

An index value would accomplish this, but I don't know how to get this via a MEASUREAn index value would accomplish this, but I don't know how to get this via a MEASURE

 

 

Hi @ccakjcrx,

 

If you column stored the normal date value, you can try to use below measure to calculate the rank as the index:

Index =
COUNTX (
    FILTER ( ALL ( Table ), [Month Year] <= MAX ( [Month Year] ) ),
    [Month Year]
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

@v-shex-msft

 

Hey!

 

Thanks for helping me out.

 

I applied your measure, but got the 14 for each row--rather than showing 1 for the first row, 2 for the second row, etc. You can download my .pbix file HERE

 

Index Suggestion.jpg

 

 

 

 

 

 

 

 

 

 

 

 

I want to do this in a MEASURE, but I'm not sure it is possible 😞

Hi @ccakjcrx

 

Try this MEASURE

 

Index =
RANKX (
    ALLSELECTED ( Sheet1[Month Year] ),
    CALCULATE ( SELECTEDVALUE ( Sheet1[Month Year] ) ),
    ,
    ASC
)

Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad ,

I have tried this, but this does gives the desired ranking.

Please refer the image below

Screenshot 2023-02-01 160927.png

Please, let me know your valuable suggestions.
Thank you 🙂

@Zubair_Muhammad

 

Hey! 

 

That works like a charm. Thank you very much!!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.