DAX EQUATION

[hosea_chumba]

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

[daXtreme]

You 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.

[PurpleGate · Accepted solution]

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