Amount is not populated correctly with multiple conditions

[Anonymous]

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])),

                0,  

                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

[Greg_Deckler]

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

[Anonymous]

@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),

        0,

        TRUE(),

        SUMX(

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

            'Claims List'[Total Incurred Net]

        )

    )

[Greg_Deckler · Accepted solution]

@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]
        )
    )

[Anonymous]

@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."

[Greg_Deckler]

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

[Anonymous]

@Greg_Deckler yes, it is calculated column. 

[Greg_Deckler]

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

[Anonymous]

@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

    )