Calculated table using a measure to calculate data based on a slicer .

[Razan_Omoush]

I created a measure to calculate an amount based on multiple conditions, including one that calculates the amount for a specific period using a date slicer. However, when I apply an additional slicer to filter data based on a specific value (for example, from the "Policy" dimension table), the measure returns a blank result. If I use the measure without any slicers on the dimension tables, the amount calculates correctly as shown in the images below. It seems like the problem is in the variable "Above Zero". Is there any solution for this. Thank you in advance.

-------------

OSAmountnot13 =

 VAR MAX_DATE = LASTDATE('Date'[Date])

 VAR AboveZero = SELECTCOLUMNS(

            FILTER(

                CALCULATETABLE(

                    SUMMARIZE(

                        'FACT',

                        'FACT'[CLAIM_ID],

                        "Amount", SUM('FACT'[OS_AMOUNT])

                    ),

                    FILTER(

                        'FACT',

                        'FACT'[UW_DATE] <= MAX_DATE

                    ),

                    FILTER(

                        'FACT',

                        'FACT'[OVERRIDE_STATUS] <> "13"

                    ),

                    FILTER(

                        'FACT',

                        'FACT'[BENEFIT_TYPE] = 1

                    )

                ),

                [Amount] > 0

            ),

            "CLM_ID", 'FACT'[CLAIM_ID]

        )

        VAR MED_os_not_13 = CALCULATE(

            SUM('FACT'[os_amount]),

            'FACT'[UW_DATE] <= MAX_DATE,

            REMOVEFILTERS('FACT'[UW_DATE]),

            NOT ('FACT'[OVERRIDE_STATUS] = "13"),

            'FACT'[BENEFIT_TYPE] = 1,

            'FACT'[CLAIM_ID] IN AboveZero

        )

        RETURN

            MED_os_not_13

-----------------

[Ashish_Mathur]

Hi,

Share some data to work with, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[Anonymous]

Hi @Razan_Omoush ,

Based on the description, None of your expressions involve the policy_no column in the slicer visual, is there a relationship between the policy table and the fact table? So, try to check the relationship and use ALLSELECTED to remove filter.

Best Regards,

Wisdom Wu

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

[shashidhar]

Try this 
OSAmountnot13 =
VAR MAX_DATE = LASTDATE('Date'[Date])
VAR AboveZero = SELECTCOLUMNS(
FILTER(
CALCULATETABLE(
SUMMARIZE(
'FACT',
'FACT'[CLAIM_ID],
"Amount", SUM('FACT'[OS_AMOUNT])
),
'FACT'[UW_DATE] <= MAX_DATE,
'FACT'[OVERRIDE_STATUS] <> "13",
'FACT'[BENEFIT_TYPE] = 1
),
[Amount] > 0
),
"CLM_ID", 'FACT'[CLAIM_ID]
)

VAR MED_os_not_13 = CALCULATE(
SUM('FACT'[os_amount]),
'FACT'[UW_DATE] <= MAX_DATE,
'FACT'[OVERRIDE_STATUS] <> "13",
'FACT'[BENEFIT_TYPE] = 1,
'FACT'[CLAIM_ID] IN AboveZero,
REMOVEFILTERS('FACT'[UW_DATE]), -- To avoid conflicts with date slicer
REMOVEFILTERS('Policy') -- To handle slicer interaction
)

RETURN
MED_os_not_13