topic Chart Granularity in DAX Commands and Tips

Chart Granularity

Diabetic — Tue, 29 Oct 2024 12:01:42 GMT

I have a graph that shows the Offered, Answered & Abandoned calls from my data set. I have this on the y-axis of a bar chart and then I have created a parameter for the dates on the x-axis. This allows me to use a slicer to swap between the granularity levels (Daily, Weekly, Monthly).To achieve this, I have a date table that has all the dates for this year, along with a column defining the month and a column defining the week commencing date.

I want the Daily view to show the last 30 days, the weekly view to show the last 8 weeks and the monthly view to show the last 12 months. This is where I am running into an issue. I can't get the dates to change with the granularity which causes my data to show an entire year of data on all the views.

How can I change this so the date range of the displayed data aligns with the desired granularity that is being set by the slicer?

Re: Chart Granularity

johnt75 — Tue, 29 Oct 2024 14:20:54 GMT

You can try creating a measure which you would use in the filter pane, set to only show when the value is 1.

Filter Measure = VAR FirstVisibleDate = MIN ( 'Date'[Date] ) VAR LastVisibleDate = MAX ( 'Date'[Date] ) VAR LastDateToShow = TODAY () VAR __SelectedGranularity = SELECTCOLUMNS ( SUMMARIZE ( Parameter, Parameter[Parameter], Parameter[Parameter Fields] ), Parameter[Parameter] ) VAR FirstDateToShow = IF ( COUNTROWS ( __SelectedGranularity ) = 1, SWITCH ( __SelectedValue, "Daily", LastDateToShow - 30, "Monthly", EOMONTH ( LastDateToShow, -13 ) + 1, "Weekly", LOOKUPVALUE ( 'Date'[Week commencing], 'Date'[Date], LastDateToShow - 7 * 8 ) ) ) VAR Result = IF ( FirstVisibleDate >= FirstDateToShow && LastVisibleDate <= LastDateToShow, 1 ) RETURN Result

Re: Chart Granularity

Diabetic — Tue, 29 Oct 2024 16:05:53 GMT

Excellent, that worked brilliantly. Thank you