Forum Discussion
CALCULATE function result changes by using slicer
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
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!
15 Replies
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
I tried. Using All(TableX) as another Filter in CALCULATE unfortunately leads to no filtering at all.
What is the column you are using as the filter in the slicer?
put ALL on that column
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.
