Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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
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
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |