topic Re: aggregate chart in DAX Commands and Tips

aggregate chart

hodobee — Wed, 15 Jan 2025 15:15:56 GMT

Hi!

I have a problem I can't solve. I have a January aggregate chart and a February aggregate chart. If January and February are selected together in the filter it works fine. However, if I select only February, then the slices where there was no value for January are dropped. I have turned off the interaction between the filter and the chart and put a measure on the max date, but that way it counts with the max date, not February.
Or it doesn't work with that either:

calc_chart =
VAR max_month = Max('A'[date])
RETURN
CALCULATE(
DISTINCTCOUNT('A'[number]),
FILTER(
ALL('A'),
'A'[result] = "closed" &&
'A'[date] <= max_month &&
(ISBLANK(SELECTEDVALUE('A'[where])) || 'A'[where] = SELECTEDVALUE('A'[where]) &&
(ISBLANK(SELECTEDVALUE('A'[year

])) || 'A'[year] = SELECTEDVALUE('A'[year]))

)))
January:

January+February
Just February, dropped 2 slice 😞
Thanks

Re: aggregate chart

Anonymous — Thu, 16 Jan 2025 02:15:03 GMT

Hi hodobee ,

Based on the testing, creating the date table to filter the chart.

Dates = CALENDAR(MIN('A'[date]), MAX('A'[date]))

Then, try using the following DAX formula to calculate the aggregate based on the selected month.

calc_chart = VAR max_month = MAX('Dates'[Date]) RETURN CALCULATE( DISTINCTCOUNT('A'[Number]), FILTER( ALL('A'), 'A'[Result] = "closed" && 'A'[Date] <= max_month && (ISBLANK(SELECTEDVALUE('A'[where])) || 'A'[where] = SELECTEDVALUE('A'[where])) && (ISBLANK(SELECTEDVALUE('A'[Year])) || 'A'[Year] = SELECTEDVALUE('A'[Year])) ) )

Drag the date table month column to the slicer visual.

Select the month. The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.