how to set missing shocks to base shock in DAX

[Lisa_2021]

typically the data include Shock Index 1-17. 

However, the data for claims only included Shock Index 17; Shock Index 1-16 are missing.

For claims, I want to set missing Shock Index1-16 to the value in Shock Index 17.  When I tried to set Shock Index 1 using this, it doesn't work
" [measure_Strategy] = 2   && [Shock Index] = 1, claims_base"

However, when I use a number like 80, it worked.
" [measure_Strategy] = 2   && [Shock Index] = 1, 80"

Here are my complete DAX:
------------------------------ 

VAR base =

    CALCULATE (

        SUMX (

            'Table',

            'Table'[final_value]

        ),

        'Table'[Shock Index] = 17,

    )

VAR not_base =

    CALCULATE (

        SUMX (

            'Table',

            'Table'[final_value]

        ),

        'Table'[Shock Index] <> 17,

    )

VAR claims_base =

    CALCULATE (

        SUMX (

            'Table',

            'Table'[final_value]

        ),

        'Table'[Shock Index] = 17,

        'Table'[Strategy] = 2

    )

RETURN

    SWITCH (

        TRUE (),

        [measure_scenario]= 17,  base + 0,

        [measure_strategy] = 2  && [scenario] = 1, claims_base,

        [measure_scenario] <> 17,   base + not_base + 0

    )


Thank you!

[Lisa_2021]

Maybe I need to break data lineage for shock index so shock 17 can be added to missing shock 1-16

[Lisa_2021]

I'm guessing it didn't work because the table didn't include any data for shock 1-16 for claims.  Thus it doesn't know how to add base to shock 1 because claims - shock 1 doesn't exist.

[Lisa_2021]

Thank you!  While looking through your suggestion, I realized that my measure_scenario is wrong.  I don't know what to use.  I was using AVERAGEX but that gives me shock/scenario = 7

Because SWITCH requires a measure and not a column, I needed to created a measure that can identify Shock 1-17 so I can set missing shock 1-16 to shock 17 for claims.  (Shock and scenario are referring to the same column)  

[123abc]

If you need to create a measure that identifies Shock Index 1-17 and set missing Shock Index 1-16 to Shock Index 17 for claims, you can create a new measure to accomplish this. Here's how you can do it:

MissingShocksToBase =
VAR CurrentShock = MAX('Table'[Shock Index])
VAR IsClaim = MAX('Table'[Strategy]) = 2
RETURN
IF (
CurrentShock = 17 || IsClaim,
SUM('Table'[final_value]),
CALCULATE(
SUM('Table'[final_value]),
'Table'[Shock Index] = 17,
IsClaim
)
)

In this measure, we check the current shock (Shock Index) and whether it's a claim (Strategy equals 2). If the shock is 17 or it's a claim, we simply sum the 'final_value' for that row. Otherwise, we calculate the sum of 'final_value' for rows where Shock Index is 17 and it's a claim (Strategy equals 2).

Now, you can use this measure in your SWITCH statement to handle missing shocks:

RETURN
SWITCH (
TRUE (),
[measure_scenario] = 17, base + 0,
[measure_strategy] = 2 && [Shock Index] = 1, [MissingShocksToBase],
[measure_scenario] <> 17, base + not_base + 0
)

This way, when you have Shock Index 1-16 missing for claims, it will use the MissingShocksToBase measure to set them to the value of Shock Index 17.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Lisa_2021]

I created a sample pbix.  Not sure how to upload it here

[123abc]

You can upload your PBIX file in one direv with G-Mail account. 

[123abc]

No problem, I was away for 5 days but I'm back in my hometown now. Please provide a detailed description of the issue, and I'll do my best to address it promptly.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Lisa_2021]

Thank you for continuing to assist me.  Really appreciate it.

[Lisa_2021]

Hi 123abc,

thank you!  I tried creating a new measure 'MissingShocksToBase'

and got this following error message:
The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

[123abc]

If you want to create a measure that identifies Shock Index values from 1 to 17 and use it to set missing Shock Index 1-16 to Shock Index 17 for claims, you can create a new measure in your Power BI model. Here's how you can create such a measure:

1. Open Power BI Desktop.

2. In the "Model" view, locate your data table (e.g., 'Table').

3. Click on "New Measure" in the formula bar.

4. Define your new measure using DAX. You can use the following DAX expression as an example to create a measure named "Shock Index 1-17":

Shock Index 1-17 =
VAR CurrentShock = MAX('Table'[Shock Index])
RETURN
IF(
CurrentShock >= 1 && CurrentShock <= 17,
CurrentShock,
17
)

This measure checks if the current Shock Index is between 1 and 17. If it is, it returns the current Shock Index value. If the current Shock Index is outside this range (missing values), it returns 17.

1. Click on the checkmark to create the measure.

Once you have created the "Shock Index 1-17" measure, you can use it in your DAX calculation to set missing Shock Index 1-16 to Shock Index 17 for claims. Here's an example of how your DAX calculation might look with the new measure:

VAR base =
CALCULATE (
SUMX (
'Table',
'Table'[final_value]
),
'Table'[Shock Index] = 17
)

VAR not_base =
CALCULATE (
SUMX (
'Table',
'Table'[final_value]
),
'Table'[Shock Index] <> 17
)

VAR claims_base =
CALCULATE (
SUMX (
'Table',
COALESCE('Table'[final_value], CALCULATE(SUM('Table'[final_value]), 'Table'[Shock Index] = 17))
),
'Table'[Shock Index] = 17,
'Table'[Strategy] = 2
)

RETURN
SWITCH (
TRUE (),
[Shock Index 1-17] = 17, base + 0,
[measure_strategy] = 2 && [Shock Index 1-17] = 1, claims_base,
[Shock Index 1-17] <> 17, base + not_base + 0
)

Now you're using the "Shock Index 1-17" measure to identify Shock Index values and set missing Shock Index 1-16 to 17 for claims in your DAX calculation.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Lisa_2021]

Hi,
I created the new measure Shock Index 1-17 and updated the DAX calculation and unfortunately "not based" are still showing 0 instead of the base.

[123abc]

It looks like you want to set missing Shock Index values (1-16) to the value of Shock Index 17 for claims when the measure strategy is equal to 2. The issue you're facing seems to be related to how you're specifying the condition for Shock Index 1. You mentioned that it works when you use a number like 80, but it doesn't work when you use Shock Index 1.

In DAX, you can't use a direct equality comparison (e.g., [Shock Index] = 1) to set the value conditionally. Instead, you should use a combination of functions to achieve this. You can use the IF or SWITCH function to conditionally set the Shock Index values. Here's an updated version of your DAX code:

VAR base =
CALCULATE (
SUMX (
'Table',
'Table'[final_value]
),
'Table'[Shock Index] = 17,
)

VAR not_base =
CALCULATE (
SUMX (
'Table',
'Table'[final_value]
),
'Table'[Shock Index] <> 17,
)

VAR claims_base =
CALCULATE (
SUMX (
'Table',
'Table'[final_value]
),
'Table'[Shock Index] = 17,
'Table'[Strategy] = 2
)

RETURN
SWITCH (
TRUE (),
[measure_scenario] = 17, base + 0,
[measure_strategy] = 2 && [Shock Index] = 1, claims_base,
[measure_scenario] <> 17, base + not_base + 0
)

In this updated code, I modified the condition for Shock Index 1 within the SWITCH statement to use the && operator to check both the strategy and Shock Index. [measure_strategy] = 2 && [Shock Index] = 1 ensures that the condition is met for Shock Index 1 when the strategy is 2.

This should set missing Shock Index values (1-16) to the value in Shock Index 17 when the strategy is 2 for claims.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Lisa_2021]

sorry for the delay!  the notification email went to junk folder so I didn't realized that you've responded.

unfortunately I tried your new DAX code and it still didn't work.   Not sure what's wrong.