Forum Discussion
Cumulative chart with filter
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!
5 Replies
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.
When I had a slicer on region, or any other column, it didn't filter the cumulative chart.
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.
Yes, you've right, I've to check that better.
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?
