Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 !
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |