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
Anonymous
Not applicable

DAX and dates and rank

Hi, I have this table. I Also have an external date table. In this case, I have selected the date 30.10.2018. I want the 3 rows in this table to be filtered out when selecting this date, because rowno 1 and Rowno 2 have the same startdate, therefore the row with the latest EndDate within these 2 rows will "Win". The startdate and enddate in the 3rd row isn't between the selected date and will be filtered out as well.

 

I tried Rank/Earlier on the startdate, But I also need the rank to look into the enddate and see which of the 2 rows have the latest enddate.

 

I have Scribbled the wanted result in attachment below. 

 

Anoyone?

 

I tried this:

RANK=RANKX('Table','table'[startdate] && 'Table'[Enddate],,ASC,dense)

 

Skjermbilde.PNG

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

You may try the DAX below.

Column =
RANKX ( Table1, Table1[StartDate],, ASC, SKIP )
    + RANKX (
        FILTER ( Table1, Table1[StartDate] = EARLIER ( Table1[StartDate] ) ),
        Table1[EndDate],
        ,
        ASC,
        SKIP
    )
    - 1
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@Anonymous,

 

You may try the DAX below.

Column =
RANKX ( Table1, Table1[StartDate],, ASC, SKIP )
    + RANKX (
        FILTER ( Table1, Table1[StartDate] = EARLIER ( Table1[StartDate] ) ),
        Table1[EndDate],
        ,
        ASC,
        SKIP
    )
    - 1
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks! 🙂

 

This worked.

 

I also tried

 

RANKX('Table';[StartDate]+[Enddate];;ASC;DENSE) This gave the same result in this case. 

 

Regards

Terje

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.