CALCULATE function result changes by using slicer

[nick1097]

Hello all,

I hope you can help, I think it is just a matter of understanding the filter in CALCULATE.

I am currently using a calculate function and I can´t explain the result myself. For example in the following measure I want to calculate the average of all process times for product type "B".

Process Time B = 
CALCULATE(
    AVERAGE(
        TableX[Process Time]
    ), 
    TableX[Product] = "B"
)

In general and without other slicers the results seems right (let´s say 6,5h). But once I add a slicer, e.g. for product type, and select other product types than "B" the value changes.

My understanding though was that CALCULATE overrules any other filters. So I expected the card-visual for "Process Time B" to show 6,5h regardless of the settings in the product-type-slicer, because it is "Product B"-specific. Nevertheless, the result for Process Time B changes.

How can that be? I would appreciate any help.

Kind regards

Nick

[nick1097]

I tried. Using All(TableX) as another Filter in CALCULATE unfortunately leads to no filtering at all.

[Gabry]

What is the column you are using as the filter in the slicer?

put ALL on that column

[nick1097]

It doesn´t solve it.

But maybe back to my original question: Why does the slicer (Product Type) affect the result of my CALCULATE function, where I already filtered product type to be "B"? For example when I am slicing product type "A" only.

Side note: When I am slicing product type "A" and "B" the value is correct again (like slicing product type "B" only).

I understand that other filters have influence on the measure. But since product type is already filtered in the measure, I expected the measure to overrule the product type slicer at least.

In the example: I expected the correct value. But instead it is a value not far from the correct one and I can´t explain that.

[Gabry]

As far as I understand, if the filter is applied to the same column that is also used in the slicer, the slicer's selection should be overwritten by the calculate filter.

Maybe try also this:

Process Time B =
CALCULATE(
AVERAGE(
TableX[Process Time]
),

Filter(All(TableX),
TableX[Product] = "B",)

)

[nick1097]

Again, this leads to no filtering at all.

[Gabry]

Process Time B =
CALCULATE(
AVERAGE(
TableX[Process Time]
),

Filter(All(TableX[Product]),
TableX[Product] = "B",)

)

if not paste the pbix

[nick1097]

I oversaw something. It doesn´t work after all.

[Gabry]

Sounds really strange to me. Maybe paste the pbix I don't know

[nick1097 · Accepted solution]

I found the problem. The value was influenced by the date-slicer, that was referencing the date column of the fact table. Since I created a date dimension table and changed the reference of the slicer, it works with only a CALCULATE function!

[nick1097]

That works!

Is there any possibility to do that without a FILTER-function to save performance? That was also my initial objective.

[Gabry]

Already tried this?

Process Time B =
CALCULATE(
AVERAGE(
TableX[Process Time]
),

All(TableX[Product]),
TableX[Product] = "B"

)

Do you really have performance issue using filter or it's just speculation?

As i know every filter put in the calculate functions it gets converted behind the scenes in a filter so

[nick1097]

First of all, thank you for your time and help!

I tried this already. With that it shows the same values like the original code:

Process Time B = 
CALCULATE(
    AVERAGE(
        TableX[Process Time]
    ), 
    TableX[Product] = "B"
)

[Gabry]

Do you have a DIM tabel or you are just filtering from the fact column table?

Tablex is the fact or the dim?

Paste the screen of the model.

Anyway you probably need to put an all somewhere, maybe on the dim table

[nick1097]

The data model is really simple. All data for this described case is in the fact table (TableX). I am not using data from dimension tables.

[Gabry]

just try this then
Process Time B =
CALCULATE(
AVERAGE(
TableX[Process Time]
),
TableX[Product] = "B",

All(TableX)
)