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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors