topic Re: CALCULATE function result changes by using slicer in Desktop

CALCULATE function result changes by using slicer

nick1097 — Thu, 11 Apr 2024 12:55:51 GMT

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

Re: CALCULATE function result changes by using slicer

Gabry — Thu, 11 Apr 2024 12:59:29 GMT

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

Re: CALCULATE function result changes by using slicer

nick1097 — Thu, 11 Apr 2024 13:05:15 GMT

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.

Re: CALCULATE function result changes by using slicer

Gabry — Thu, 11 Apr 2024 13:07:04 GMT

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

All(TableX)
)

Re: CALCULATE function result changes by using slicer

nick1097 — Thu, 11 Apr 2024 13:20:46 GMT

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

Re: CALCULATE function result changes by using slicer

Gabry — Thu, 11 Apr 2024 13:31:04 GMT

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

put ALL on that column

Re: CALCULATE function result changes by using slicer

nick1097 — Fri, 12 Apr 2024 08:21:50 GMT

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.

Re: CALCULATE function result changes by using slicer

Gabry — Fri, 12 Apr 2024 08:46:41 GMT

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

)

Re: CALCULATE function result changes by using slicer

nick1097 — Fri, 12 Apr 2024 09:04:22 GMT

Again, this leads to no filtering at all.

Re: CALCULATE function result changes by using slicer

Gabry — Fri, 12 Apr 2024 10:52:08 GMT

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

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

)

if not paste the pbix

Re: CALCULATE function result changes by using slicer

nick1097 — Fri, 12 Apr 2024 14:22:58 GMT

~~That works!~~

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

Re: CALCULATE function result changes by using slicer

Gabry — Fri, 12 Apr 2024 12:41:21 GMT

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

Re: CALCULATE function result changes by using slicer

nick1097 — Fri, 12 Apr 2024 14:20:54 GMT

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" )

Re: CALCULATE function result changes by using slicer

nick1097 — Fri, 12 Apr 2024 14:22:33 GMT

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

Re: CALCULATE function result changes by using slicer

Gabry — Fri, 12 Apr 2024 14:37:27 GMT

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

Re: CALCULATE function result changes by using slicer

nick1097 — Mon, 15 Apr 2024 13:27:45 GMT

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!