Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all, thank you for your kind replies!! I managed to get the results using the formula below after several attempts 🙂
Formula:
Hi all, thank you for your kind replies!! I managed to get the results using the formula below after several attempts 🙂
Formula:
Hi @Anonymous,
The way you tried to build this is via a nested if. This is possible, but the best solution is using @vcastello's Switch function here since its faster on execution and also much easier to understand for a large number of possible criteria. If you really want this as a nested if, it should look something like this:
If( Arrears[TCMRScoring_ArrearsAcct %] < 3 = 5,
If( Arrears[TCMRScoring_ArrearsAcct %] < 4 = 4,
If( Arrears[TCMRScoring_ArrearsAcct %] < 5 = 3,
If( Arrears[TCMRScoring_ArrearsAcct %] < 6 = 2, 1
)
)
)
)
The difference with your function being that you tried to use OR ( || ) instead of inputting a new If-function as the FALSE result of the previous If-function.
As @vcastello mentioned, you should wrap Arrears[TCMRScoring_ArrearsAcct %], as the above will now give an error if there are multiple values for Arrears[TCMRScoring_ArrearsAcct %] in the current filter context. So for example, you could test a Min(Arrears[TCMRScoring_ArrearsAcct %]) to ensure the maximum 'points' are awarded based on the smallest Arrears[TCMRScoring_ArrearsAcct %] in the current context. The nested if would then look something like this:
If( Min( Arrears[TCMRScoring_ArrearsAcct %] ) < 3, 5,
If( Min( Arrears[TCMRScoring_ArrearsAcct %] ) < 4, 4,
If( Min( Arrears[TCMRScoring_ArrearsAcct %] ) < 5, 3,
If( Min( Arrears[TCMRScoring_ArrearsAcct %] ) < 6, 2, 1
)
)
)
)
You can replace the Min() function with whatever function you actually need for your specific problem, but some way to handle the possibility of multiple values for Arrears[TCMRScoring_ArrearsAcct %] in your current filter context is necessary.
Hi @Anonymous
You can try SWITCH.
Should something like this work?
SWITCH(
TRUE (),
VALUES(Arrears[TCMRScoring_ArrearsAcct %]) < 3, 5,
VALUES(Arrears[TCMRScoring_ArrearsAcct %]) < 4, 4,
VALUES(Arrears[TCMRScoring_ArrearsAcct %]) < 5, 3,
VALUES(Arrears[TCMRScoring_ArrearsAcct %]) < 6, 2,
1
)
Anyway, your Arrears[TCMRScoring_ArrearsAcct %] should be wrapped. With VALUES, SUM, or other function
Hope That Helps
Vicente
I do it the like this:
Name = IF('Table'[Column] = "" ; "" ; "" ) & IF('Table'[Column] = "" ; "" ; "" ) & IF
etc ...
+ You need to be more specific, because things that are ❤️ can be <4 aswell, if you understand what i mean