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.
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
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 |
---|---|
115 | |
100 | |
90 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |