Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
I'm trying to work on a Country Ranking Report and to write a DAX Measure which would help me to see what the Difference in Rank of a Country is from it's rank in the previous year.
Sample Data
Country | Rank | Year |
A | 1 | 2017 |
B | 2 | 2017 |
C | 3 | 2017 |
B | 1 | 2018 |
A | 2 | 2018 |
C | 3 | 2018 |
B | 1 | 2019 |
C | 2 | 2019 |
A | 3 | 2019 |
Wanted Result
slicer selection : year 2018
Country | Rank | Year | Difference From Prev Year |
B | 1 | 2018 | +1 |
A | 2 | 2018 | -1 |
C | 3 | 2018 | 0 |
Also there are some countries in the dataset that are ranked in a particular year but maybe not in the year before or next so hence there's no record of that country alongside those years after or before. If possible would there be a way to show that Country's Rank difference as [N.R] in that particular case.
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
Rank measure: =
IF( HASONEVALUE(country_dim[Country]), MAX(rank_fct[Rank]))
Diff from prev year measure: =
VAR _prevyear =
MAX ( year_dim[Year] ) - 1
VAR _prevyearrank =
CALCULATE ( [Rank measure:], year_dim[Year] = _prevyear )
RETURN
IF (
NOT ISBLANK ( [Rank measure:] ) && NOT ISBLANK ( _prevyearrank ),
SWITCH (
TRUE (),
[Rank measure:] > _prevyearrank,
"+ " & [Rank measure:] - _prevyearrank,
[Rank measure:] < _prevyearrank,
"- " & _prevyearrank - [Rank measure:],
"0"
),
"NR"
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
It worked! Thank you so much.
Hi,
Please check the below picture and the attached pbix file.
Rank measure: =
IF( HASONEVALUE(country_dim[Country]), MAX(rank_fct[Rank]))
Diff from prev year measure: =
VAR _prevyear =
MAX ( year_dim[Year] ) - 1
VAR _prevyearrank =
CALCULATE ( [Rank measure:], year_dim[Year] = _prevyear )
RETURN
IF (
NOT ISBLANK ( [Rank measure:] ) && NOT ISBLANK ( _prevyearrank ),
SWITCH (
TRUE (),
[Rank measure:] > _prevyearrank,
"+ " & [Rank measure:] - _prevyearrank,
[Rank measure:] < _prevyearrank,
"- " & _prevyearrank - [Rank measure:],
"0"
),
"NR"
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
59 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
87 | |
77 | |
52 | |
37 | |
21 |