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

Returning the value of a 5th item from a table in a calculated measure

Hi guys, 

 

I'm faced with a problem I cannot seem to be able to crack. I would like to create a measure that would return the 5th latest date from this table:

BusinessDate20190501
20190430
20190429
20190426
20190425
20190424
20190423
20190422
20190419
20190418

Meaning that I would like to create a calculated measure that returns 20190425 .

 

So first I wanted to rank the values in the table. For that I used following DAX statement:

Rank BD:=
RANKX(
    ALL(BD[businessdateid]),
    SUM(BD[businessdateid]),
    ,
    0,
    Skip
)

This measure works fine when you place the businessdateid on the dimension. Then the evaluation context is ok and for each businessdateid it shows the correct rank. The problem is I would like to use this [RANK BD] without using the businessdateid as a dimension in the cube to return the value. So i have written the following DAX:

myval:=
CALCULATE(
    MAX(BD[businessdateid]),
    FILTER(ALL('BD'[businessdateid]), [Rank BD]=5)
)

This as expected doesn't show anything if you dont explicitly drag the businessdateid into an axis, as from what i can understand it takes ALL businessdateid values together and sums their values into one, always resulting in RANK = 1. 

Is it possible somehow to enforce the evaluation context in the formula so that it "loops" over businessdateid and picks the 5th?

 

Many thanks in advance for your help on this one!

1 ACCEPTED SOLUTION
technolog
Super User
Super User

Hello,

To achieve the desired outcome of returning the 5th latest date from your table in a calculated measure without explicitly using businessdateid as a dimension, you can modify your DAX approach. The key is to construct a measure that can correctly identify the 5th latest date based on your existing ranking.

Given the scenario you've described, your current ranking measure seems fine, but the challenge lies in applying this rank to retrieve the specific date. The following DAX formula should help you in achieving this:


FifthLatestDate :=
VAR RankedDates =
ADDCOLUMNS(
ALL('BD'),
"Rank", RANKX(ALL('BD'), [businessdateid], , DESC, Dense)
)
VAR FifthDate =
CALCULATE(
MAX('BD'[businessdateid]),
FILTER(RankedDates, [Rank] = 5)
)
RETURN
FifthDate

View solution in original post

1 REPLY 1
technolog
Super User
Super User

Hello,

To achieve the desired outcome of returning the 5th latest date from your table in a calculated measure without explicitly using businessdateid as a dimension, you can modify your DAX approach. The key is to construct a measure that can correctly identify the 5th latest date based on your existing ranking.

Given the scenario you've described, your current ranking measure seems fine, but the challenge lies in applying this rank to retrieve the specific date. The following DAX formula should help you in achieving this:


FifthLatestDate :=
VAR RankedDates =
ADDCOLUMNS(
ALL('BD'),
"Rank", RANKX(ALL('BD'), [businessdateid], , DESC, Dense)
)
VAR FifthDate =
CALCULATE(
MAX('BD'[businessdateid]),
FILTER(RankedDates, [Rank] = 5)
)
RETURN
FifthDate

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.

Top Solution Authors