SSAS DAX query.... how to show totals against attribute values that match calc conditions

Question

In our companies cubes we regularly use the CALCULATION function for our measures. We also regularly come across the scenario where even though we apply a filter in the CALCULATION the returned total shows the same value across multiple rows instead of the rows where the condition is met.

The below screen shot is an example.

Where we want the total to only be display against the Row where the Attribute = "Y"

The CALCULATION

=
CALCULATE (
    DISTINCTCOUNT ( 'Sales'[Transaction_Header_ID] ),
    'Sales'[Is_Named_Sale] = "Y",
    'Member'[Member_Has_Email_Flag] = "Y"
)

I am fairly new to DAX and still don't understand how when you use filters in a calculation this can occur. The result we are after is more like

Member_Has_Email_Flag Total

Y                     249239

N

If anyone can explain this it would be greatly appreciated.

Cheers

RADO RADO · Accepted Answer · 2020-02-20T06:32:17

Think what happens in the report cell where 'Member'[Member_Has_Email_Flag] is "N".

From the report, we are getting a filter 'Member'[Member_Has_Email_Flag] = "N". Then, inside CALCULATE function, you are overriding this filter with "Y". So, you formula ignores filters coming from the report, and replaces them with "Y", giving you the same number regardless of the external filter. That's how CALCULATE works - it modifies external filters by replacing them with the filters you provide inside the function.

To fix the the problem, use KEEPFILTERS function:

=
CALCULATE (
    DISTINCTCOUNT ( 'Sales'[Transaction_Header_ID] ),
    'Sales'[Is_Named_Sale] = "Y",
    KEEPFILTERS('Member'[Member_Has_Email_Flag] = "Y")
)

Here, you tell CALCULATE to keep the external filter ('N") and combine it with the new filter ("Y"). Since there are no records where this field is simultaneously Y and N, the formula will now return blank.

SSAS DAX query.... how to show totals against attribute values that match calc conditions

1 Answers