topic Re: Amount is not populated correctly with multiple conditions in DAX Commands and Tips

Amount is not populated correctly with multiple conditions

Anonymous — Tue, 17 Oct 2023 14:51:39 GMT

I have two file in Power BI, one is claims list and one is incident register.

I created "Updated Claim Amount" column with DAX Function as per below:

Updated Claim Amount =

VAR CurrentQBE = 'Incident Register'[QBE Number]

RETURN

IF(

ISBLANK(CurrentQBE),

[Cost to Keenan Repair/Recovery] + 'Incident Register'[Cost to TP/property damage],

IF(

SELECTEDVALUE('Claims List'[Claim Status]) = "No Claim",

SUMX('Incident Register', [Cost to Keenan Repair/Recovery] + 'Incident Register'[Cost to TP/property damage]),

IF(

ISBLANK(LOOKUPVALUE('Claims List'[Total Incurred Net],'Claims List'[Broker Ref],'Incident Register'[QBE Number])),

LOOKUPVALUE('Claims List'[Total Incurred Net],'Claims List'[Broker Ref],'Incident Register'[QBE Number])

)

Everything works fine except when the outcome is 'QBE is not blank' and the claim status is 'no claim.' It should add 'Cost to Keenan Repair/Recovery' + 'Incident Register'[Cost to TP/property damage], but it shows as 0.

Sample data from claims list report:

Broker Ref	Claim Status	Total Incurred Net
QBE04029610	Open	£1,999.00
QBE04029947	Open	£5,398.00
QBE04028368	No Claim	£0.00
QBE04027518	Open	£13,142.90
QBE04022177	Open	£3,999.00
QBE04021461	Open	£7,211.02
QBE04020829	Open	£7,921.92
QBE04024895	Open	£5,039.00
QBE04020898	Open	£1,999.00
QBE04021906	Fault	£1,890.00
QBE04017127	Open	£5,348.57

Sample date from incident register:

QBE Number	Cost to Keenan Repair/Recovery	Cost to TP/property damage
	1,932.91
	3,599

QBE04008099/QBE04008142
QBE04017127
QBE04021906
QBE04024895
QBE04020898
QBE04020829
QBE04022177
QBE04021461
QBE04027518
QBE04028368
QBE04029138
QBE04029610
QBE04029947

Re: Amount is not populated correctly with multiple conditions

Greg_Deckler — Tue, 17 Oct 2023 15:02:47 GMT

Anonymous First, I would change from using nested IF statements to using SWITCH(TRUE(), ...). Will greatly cleanup your code and perhaps make it obvious where things are going awry.

Re: Amount is not populated correctly with multiple conditions

Anonymous — Tue, 17 Oct 2023 15:18:03 GMT

Greg_Deckler Thanks for your advise. I used switch function but it is having error as "A table of multiple values was supplied where a single value was expected."

Updated Claim Amount =

VAR CurrentQBE = 'Incident Register'[QBE Number]

VAR ClaimLookup = LOOKUPVALUE('Claims List'[Total Incurred Net], 'Claims List'[Broker Ref], CurrentQBE)

RETURN

SWITCH(

TRUE(),

ISBLANK(CurrentQBE),

[Cost to Keenan Repair/Recovery] + 'Incident Register'[Cost to TP/property damage],

SELECTEDVALUE('Claims List'[Claim Status]) = "No Claim",

[Cost to Keenan Repair/Recovery] + 'Incident Register'[Cost to TP/property damage],

ISBLANK(ClaimLookup),

TRUE(),

SUMX(

FILTER('Claims List', 'Claims List'[Broker Ref] = CurrentQBE),

'Claims List'[Total Incurred Net]

)

Re: Amount is not populated correctly with multiple conditions

Greg_Deckler — Tue, 17 Oct 2023 15:33:43 GMT

Anonymous Hmm, not sure what is going wrong but try this:

Updated Claim Amount = VAR CurrentQBE = 'Incident Register'[QBE Number] VAR ClaimLookup = LOOKUPVALUE('Claims List'[Total Incurred Net], 'Claims List'[Broker Ref], CurrentQBE) RETURN SWITCH( TRUE(), ISBLANK(CurrentQBE), [Cost to Keenan Repair/Recovery] + 'Incident Register'[Cost to TP/property damage], SELECTEDVALUE('Claims List'[Claim Status]) = "No Claim", [Cost to Keenan Repair/Recovery] + 'Incident Register'[Cost to TP/property damage], ISBLANK(ClaimLookup), 0, SUMX( FILTER('Claims List', 'Claims List'[Broker Ref] = CurrentQBE), 'Claims List'[Total Incurred Net] ) )

Re: Amount is not populated correctly with multiple conditions

Anonymous — Tue, 17 Oct 2023 15:43:11 GMT

Greg_Deckler thanks for your support but it is still showing error as "A table of multiple values was supplied where a single value was expected."

Re: Amount is not populated correctly with multiple conditions

Greg_Deckler — Tue, 17 Oct 2023 15:57:37 GMT

Anonymous Just to confirm this is a calculated column and not a measure, correct?

Re: Amount is not populated correctly with multiple conditions

Anonymous — Tue, 17 Oct 2023 16:00:56 GMT

Greg_Deckler yes, it is calculated column.

Re: Amount is not populated correctly with multiple conditions

Greg_Deckler — Tue, 17 Oct 2023 17:08:46 GMT

Anonymous Well, one thing you can do is to comment out ( // ) individual logical statements in your SWITCH statement until you find the offending statement.

Re: Amount is not populated correctly with multiple conditions

Anonymous — Wed, 18 Oct 2023 08:24:37 GMT

Greg_Deckler Thanks for your advise. i have fix the error but the initial problem still exist. Everything works fine except when the outcome is 'QBE is not blank' and the claim status is 'no claim.' It should add 'Cost to Keenan Repair/Recovery' + 'Incident Register'[Cost to TP/property damage], but it shows as 0.

Updated Claim Amount =

VAR CurrentQBE = 'Incident Register'[QBE Number]

VAR ClaimLookup =

SUMX(

FILTER('Claims List', 'Claims List'[Broker Ref] = CurrentQBE),

'Claims List'[Total Incurred Net]

)

RETURN

SWITCH(

TRUE(),

ISBLANK(CurrentQBE), [Cost to Keenan Repair/Recovery] + 'Incident Register'[Cost to TP/property damage],

SELECTEDVALUE('Claims List'[Claim Status]) = "No Claim", [Cost to Keenan Repair/Recovery] + 'Incident Register'[Cost to TP/property damage],

ISBLANK(ClaimLookup), 0,

ClaimLookup

)