DAX - Group By Ignore Slicers

[zfemmer]

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?

[amitchandak]

@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

[zfemmer]

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?

[AlB]

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 

[zfemmer]

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.

[AlB]

@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):

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]
    ),
    ALL ( RevenueChannel )
)

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