Forum Discussion
Solved
DAX EQUATION
Consider the Table1 below; my objective is to calculate a column/measure that identifies whether a loan amount meets or that does not meet the following condition or does not apply:
- Collateral amount must be at least 150% of the loan amount and does not apply for product ID "2222" and "4444"
My formula below for calculated column did not work, kindly assist.
Inadequately secured loans =
VAR _inadequate_security = CALCULATE(FILTER('Table1','Table1'[Collateral Amount] < 1.5*'Table1'[Loan Amount]
&& NOT('Table1'[Product ID]) IN {2222,4444})))
VAR _result_ = SWITCH(TRUE(),
_inadequate_security,"Inadeqaute Security",
"Adequate Security")
RETURN
_result_
Table1:
| Loan Amount | Collateral Amount | Client ID | Product ID |
| 200 | 300 | X1 | 2222 |
| 100 | 400 | X3 | 3333 |
| 600 | 200 | X4 | 4444 |
| 300 | 450 | X6 | 5555 |
| 100 | 0 | X7 | 6666 |
| 400 | 400 | X9 | 2222 |
Hi,
How about something like this
Security Measure =
VAR __Collateral = DIVIDE(SELECTEDVALUE('Table'[Collateral Amount]),SELECTEDVALUE('Table'[Loan Amount]))
VAR __ProductID = SELECTEDVALUE('Table'[Product ID ])
VAR __result = IF(__Collateral >=1.5 && __ProductID <> 2222 && __Collateral >=1.5 && __ProductID <> 4444,"Inadequate Security","Adequate Security")
RETURN __result
2 Replies
Hi,
How about something like this
Security Measure =
VAR __Collateral = DIVIDE(SELECTEDVALUE('Table'[Collateral Amount]),SELECTEDVALUE('Table'[Loan Amount]))
VAR __ProductID = SELECTEDVALUE('Table'[Product ID ])
VAR __result = IF(__Collateral >=1.5 && __ProductID <> 2222 && __Collateral >=1.5 && __ProductID <> 4444,"Inadequate Security","Adequate Security")
RETURN __resultYou need a calculated column for this, not a measure.
[Inadequately Secured] = // calc column, not a measure IF( NOT( T[Product ID] IN {2222, 4444} ), IF( T[Collateral Amount] >= 1.5 * T[Loan Amount], "Adequate Security", "Inadequate Security" ), "Adequate Security" )If you use a measure... you won't be able to slice and dice by such values.
