Is it possible to use a slicer as a parameter to a DAX Summarize function?

Question

I have a FactLosses Table, and a DimAccumulation table. I have brought them into PowerBi and I have placed a slicer to choose which accumulation zones i am interested in.

Once the user has selected the zones, i want to perform a group by year on the losses and sum the losses into year buckets. But only on the data that applies to the zones the user picked.

I am using the following DAX code to do the group by like so...

Table = SUMMARIZECOLUMNS(FactForwardLookingAccumulation[Year], "Losses By Year", SUM(FactForwardLookingAccumulation[Net Loss Our Share Usd]))

The problem is the new table always produces the same result. i.e When i make changes to which accumulation perils should be included it makes no difference to the summation. (it is summing the entire table)

I'd like to use the slicer to filter the fact table and then have the DAX query run on the filtered list. Is this possible?

So you're making a new calculated table? Those are not affected by slicers unless you are using them inside of a measure. — Alexis Olson
That's how i'm currently doing it...is there another way to do it? — Danial Hughes
It depends on what your end goal is. What are you using the calculated table for? — Alexis Olson
I need to filter the fact table of losses depending on the zones the user selects. The filtered fact table needs to be grouped by year and losses summed for each year. Once i have that, then i want to take the Max value for each year. — Danial Hughes

Alexis Olson Alexis Olson · Accepted Answer · 2018-02-27T22:41:43

If you want these tables to be responsive to filters or slicers on your report, then you can't write these as calculated tables that show up under the Data tab since those are computed before any filtering happens.

To get what you want, you have to do everything inside of a measure, since those are what respond to slicers. If you're looking for the max loss year once the grouping and summing are completed, you can write a measure along these lines:

Year Max =
    VAR CalculatedTable = SUMMARIZECOLUMNS(FactForwardLookingAccumulation[Year], "Losses By Year", SUM(FactForwardLookingAccumulation[Net Loss Our Share Usd]))
    RETURN MAXX(CalculatedTable, [Losses By Year])

Writing it this way will allow the calculated table to respond to your slicers and filters.

Is it possible to use a slicer as a parameter to a DAX Summarize function?

1 Answers