Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everone,
I'm into my third week of using DAX in PowerBI and stumbled upon this hurdle where my measure only calculates certain rows but not all like what I would have hoped for.
On the screenshow below, "Decline rate next 5 years_dynamic" and "Decline Rate Dynamic_S" are both measures I have created.
"Decline Rate Dynamic_S" is just a score (1 to 5 score) that is assigned to the asset based on the magnitude of "Decline rate next 5 years_dynamic". My problem is that it assigns a score for most of the assets but left out some - see arrow picture below - and this just puzzles me. My intention is for it to assign scores for all the rows/assets.
The code I have written for "Decline Rate Dynamic_S" is
Decline Rate Dynamic_S =
VAR Score1 = CALCULATE(SELECTEDVALUE('Score Tranches Input'[Decline Rate_SA]), 'Score Tranches Input'[Index] = 1)
VAR Score2 = CALCULATE(SELECTEDVALUE('Score Tranches Input'[Decline Rate_SA]), 'Score Tranches Input'[Index] = 2)
VAR Score3 = CALCULATE(SELECTEDVALUE('Score Tranches Input'[Decline Rate_SA]), 'Score Tranches Input'[Index] = 3)
VAR Score4 = CALCULATE(SELECTEDVALUE('Score Tranches Input'[Decline Rate_SA]), 'Score Tranches Input'[Index] = 4)
VAR Score5 = CALCULATE(SELECTEDVALUE('Score Tranches Input'[Decline Rate_SA]), 'Score Tranches Input'[Index] = 5)
RETURN
IF([Decline rate next 5 years_dynamic] <= Score1, 1,
IF([Decline rate next 5 years_dynamic] <= Score2 && [Decline rate next 5 years_dynamic] > Score1, 2,
IF([Decline rate next 5 years_dynamic] <= Score3 && [Decline rate next 5 years_dynamic] > Score2, 3,
IF([Decline rate next 5 years_dynamic] <= Score4 && [Decline rate next 5 years_dynamic] > Score3, 4,
IF([Decline rate next 5 years_dynamic] > Score5, 5)))))
Any help on this would be very much appreciated.
Solved! Go to Solution.
Seems your nest If missing the condition of 4<[Decline rate next 5 years_dynamic] <=5, You can try the following DAX and see if it works
Decline Rate Dynamic_S = VAR Score1 = CALCULATE ( SELECTEDVALUE ( 'Score Tranches Input'[Decline Rate_SA] ), 'Score Tranches Input'[Index] = 1 ) VAR Score2 = CALCULATE ( SELECTEDVALUE ( 'Score Tranches Input'[Decline Rate_SA] ), 'Score Tranches Input'[Index] = 2 ) VAR Score3 = CALCULATE ( SELECTEDVALUE ( 'Score Tranches Input'[Decline Rate_SA] ), 'Score Tranches Input'[Index] = 3 ) VAR Score4 = CALCULATE ( SELECTEDVALUE ( 'Score Tranches Input'[Decline Rate_SA] ), 'Score Tranches Input'[Index] = 4 ) VAR Score5 = CALCULATE ( SELECTEDVALUE ( 'Score Tranches Input'[Decline Rate_SA] ), 'Score Tranches Input'[Index] = 5 ) RETURN IF ( [Decline rate next 5 years_dynamic] <= Score1, 1, IF ( [Decline rate next 5 years_dynamic] <= Score2, 2, IF ( [Decline rate next 5 years_dynamic] <= Score3, 3, IF ( [Decline rate next 5 years_dynamic] <= Score4, 4, 5 ) ) ) )
Seems your nest If missing the condition of 4<[Decline rate next 5 years_dynamic] <=5, You can try the following DAX and see if it works
Decline Rate Dynamic_S = VAR Score1 = CALCULATE ( SELECTEDVALUE ( 'Score Tranches Input'[Decline Rate_SA] ), 'Score Tranches Input'[Index] = 1 ) VAR Score2 = CALCULATE ( SELECTEDVALUE ( 'Score Tranches Input'[Decline Rate_SA] ), 'Score Tranches Input'[Index] = 2 ) VAR Score3 = CALCULATE ( SELECTEDVALUE ( 'Score Tranches Input'[Decline Rate_SA] ), 'Score Tranches Input'[Index] = 3 ) VAR Score4 = CALCULATE ( SELECTEDVALUE ( 'Score Tranches Input'[Decline Rate_SA] ), 'Score Tranches Input'[Index] = 4 ) VAR Score5 = CALCULATE ( SELECTEDVALUE ( 'Score Tranches Input'[Decline Rate_SA] ), 'Score Tranches Input'[Index] = 5 ) RETURN IF ( [Decline rate next 5 years_dynamic] <= Score1, 1, IF ( [Decline rate next 5 years_dynamic] <= Score2, 2, IF ( [Decline rate next 5 years_dynamic] <= Score3, 3, IF ( [Decline rate next 5 years_dynamic] <= Score4, 4, 5 ) ) ) )
That worked ! Thanks a million !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |