topic Re: Cumulative chart with filter in DAX Commands and Tips

Cumulative chart with filter

RichardMo — Fri, 05 May 2023 14:39:14 GMT

Beginner level. I have a table with columns including ‘week_start_date’, ‘region’, and 'appointment_count'.

I have a measure for a chart showing cumulative appointment_count over time, which works fine:

Cumulative measure = CALCULATE(

SUM(Table1[appointment_count]),

FILTER(ALL(Table1),

Table1[week_start_date]<=MAX(Table1[week_start_date])

))

I want to filter the cumulative chart by the ‘region’ column, but it seems you can’t filter the above code by anything except the week_start column, so I tried this:

Cumulative measure = CALCULATE(

SUM(Table1[appointment_count]),

FILTER(ALL(Table1),

Table1[week_start_date]<=MAX(Table1[week_start_date])&&

Table1[region]=SELECTEDVALUE(Table[region])

))

This gives the correct values when I filter by region, but now the default unfiltered value is wrong; it gives a very low figure that doesn’t relate to anything.

Thank you for any tips!

Re: Cumulative chart with filter

GuillaumePower — Fri, 05 May 2023 15:43:09 GMT

It is not necessary to add this filter "Table1[region]=SELECTEDVALUE(Table[region])" in the DAX command. You can use a slicer on the region which could select one or more region. You will get the right cumul.

Re: Cumulative chart with filter

RichardMo — Fri, 05 May 2023 16:16:41 GMT

When I had a slicer on region, or any other column, it didn't filter the cumulative chart.

Re: Cumulative chart with filter

GuillaumePower — Sat, 06 May 2023 06:33:13 GMT

Yes, you've right, I've to check that better.

Re: Cumulative chart with filter

GuillaumePower — Sat, 06 May 2023 06:47:03 GMT

You should use this Dax Command whith AllExcept for the region :

Cumulative Mesure = calculate(sum(Feuil1[index]),filter(ALLEXCEPT(Feuil1,Feuil1[Region]),Feuil1[index]<=MAX(Feuil1[Index])))

Have you all your Data in the same Table ? That should run well like that.

Re: Cumulative chart with filter

RichardMo — Tue, 09 May 2023 18:06:24 GMT

Great, thank you. That works when all my data is in the same table. I have another question - what if the filter was on a related region table?