topic DAX - Group By Ignore Slicers in DAX Commands and Tips

DAX - Group By Ignore Slicers

zfemmer — Thu, 15 Oct 2020 18:19:17 GMT

I am attempting to create a measure to show revenue over the past 30 days and have it ignore any date slicers on the page.

I have this slicer that works to do this, but it only shows totals instead of breaking it down by day:

M_BookedRevenueMonth = CALCULATE( SUM(RevenueChannel[BookedRevenue]), FILTER( ALL('Date'), 'Date'[Date] >= TODAY()-30 && 'Date'[Date] < TODAY() ) )

I have then created a new measure that shows me the break down by date, but I cannot figure out how to ignore the slicers within a groupby():

M_TestMeasure = CALCULATE ( SUMX ( FILTER ( GROUPBY ( RevenueChannel, RevenueChannel[SearchType], RevenueChannel[ChannelType], 'Date'[Date], 'Date'[AccountingWeek], 'Date'[WeekNumber], 'Date'[AccountingMonthEnglishAbbrYear], 'Date'[AccountingMonth], 'Date'[AccountingYear], "Revenue", SUMX ( CURRENTGROUP (), RevenueChannel[BookedRevenue] ) ), 'Date'[Date] >= TODAY()-30 && 'Date'[Date] < TODAY() ), [Revenue] ) )

Any ideas?

Re: DAX - Group By Ignore Slicers

amitchandak — Fri, 16 Oct 2020 02:43:36 GMT

zfemmer , If want to ignore the filter, best is disable interaction.

Most of the time when you give you own filter you will end up grouping data into one date.

For that you refer to this video, there is a solution for that: https://www.youtube.com/watch?v=duMSovyosXE

Re: DAX - Group By Ignore Slicers

zfemmer — Mon, 19 Oct 2020 17:49:15 GMT

The issue with this is that I have multiple measures feeding into the same visual.

So the end user selects a measure they want; WTD, MTD, YTD, or a specific date range.

I want the date range slicer to feed into the date range measure, but not the others.

Does this make sense?

Re: DAX - Group By Ignore Slicers

AlB — Mon, 19 Oct 2020 17:54:35 GMT

Hi zfemmer

Have you tried ALL( RevenueChannel) as the first argument for the GROUPBY ( )?

Otherwise add ALL( RevenueChannel) as last argument to the outermost CALCULATE( )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Re: DAX - Group By Ignore Slicers

zfemmer — Mon, 19 Oct 2020 18:01:15 GMT

Can you tell me where to put it?

I thought this was the answer, but I could not for whatever reason figure out where the ALL would go within the group by.

Re: DAX - Group By Ignore Slicers

AlB — Mon, 19 Oct 2020 18:06:56 GMT

zfemmer

In the first argument, base table, of GROUPBY ( 😞

M_TestMeasure = CALCULATE ( SUMX ( FILTER ( GROUPBY ( ALL ( RevenueChannel ), RevenueChannel[SearchType], RevenueChannel[ChannelType], 'Date'[Date], 'Date'[AccountingWeek], 'Date'[WeekNumber], 'Date'[AccountingMonthEnglishAbbrYear], 'Date'[AccountingMonth], 'Date'[AccountingYear], "Revenue", SUMX ( CURRENTGROUP (), RevenueChannel[BookedRevenue] ) ), 'Date'[Date] >= TODAY()-30 && 'Date'[Date] < TODAY() ), [Revenue] ) )

or as second argument for the CALCULATE( ) (which by the way I am not sure why you were using it):

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers