DAX needing to operate on two part complex need.

[DataDaxGuy]

I am attempting a DAX formula that will consider filter context for fields report_month and sales_rep when calculating the sum of a field. There are two main logic points at play here.

1. The values should only sum when certain people are selected and not count those that are also selected from the exclusion list. However, if those from the exclusion list are selected either independently or together and only those from the exclusion list - they are still summed. The sum of those that are not in the exclusion list only override that of the non exclusion list when they are also selected with members of the non exclusion list.

2. When a report month is selected in the filter pane - filter context is ignored. So that if I have a column chart with the report month in the x-axis and the measure in the y-axis - if I select a specific report month from the filter pane, I will still see say - 6 months of data in the chart.

The issue with this code - 1 and 2 work under all conditions except when an exclusion person is selected AND a report month is selected. In that case - the results are not static which should display the last 6 months of data.

Is this even possible with DAX to have it consider aggregating certain people over others based on who is selected in the people filter in the filter pane + keep the measure static regardless of what is selected for the report month in the filter pane?

This logic works independently. But I cannot combine them. I understand another route is to use visual slicers and use 'edit interactions' to ignore filtering. I would rather try it with a DAX formula thought because my audience is already used to using the filter pane + I do not have a ton of real-estate to add slicers. Thank you in advance.

Code:

m_static_spiff = 
VAR MaxDate2024 =
    CALCULATE(
        MAX('report_month_filter'[report_month]),
        ALL('report_month_filter'),  // Removes all filters from this table
        YEAR('report_month_filter'[report_month]) = 2024
    )
VAR StartDate = EDATE(MaxDate2024, -5)

VAR ExcludedReps = {
    "person 1", "person 2", "person 3", "person 4"
}
VAR IsIndividualRep = HASONEVALUE(all_agg_com[sales_rep])
-- If the current context has one sales rep, get that rep's name, otherwise return BLANK
VAR CurrentRep = IF(IsIndividualRep, VALUES(all_agg_com[sales_rep]), BLANK())

VAR IsExcludedRep = IsIndividualRep && CurrentRep IN ExcludedReps

RETURN
CALCULATE(
    IF(
        NOT IsIndividualRep,
        SUMX(
            FILTER(
                all_agg_com,
                NOT all_agg_com[sales_rep] IN ExcludedReps
            ),
            all_agg_com[spiff]
        ),
        IF(
            IsExcludedRep,
            CALCULATE(SUM(all_agg_com[spiff]), ALL(all_agg_com[sales_rep])),
            SUM(all_agg_com[spiff])
        )
    ),
    FILTER(
        ALL('all_agg_com'[report_month]),  // Ignore any 'report_month' slicers or filters
        'all_agg_com'[report_month] >= StartDate &&
        'all_agg_com'[report_month] <= MaxDate2024
    )
)

[Sahir_Maharaj]

Hello @DataDaxGuy,

Can you please try the following approach:

m_static_spiff_corrected = 
VAR MaxDate2024 =
    CALCULATE(
        MAX('report_month_filter'[report_month]),
        ALL('report_month_filter'),
        YEAR('report_month_filter'[report_month]) = 2024
    )
VAR StartDate = EDATE(MaxDate2024, -5)

VAR ExcludedReps = {
    "person 1", "person 2", "person 3", "person 4"
}

RETURN
CALCULATE(
    SUM(all_agg_com[spiff]),
    FILTER(
        ALL('all_agg_com'[report_month]),
        'all_agg_com'[report_month] >= StartDate &&
        'all_agg_com'[report_month] <= MaxDate2024
    ),
    FILTER(
        all_agg_com,
        IF(
            HASONEVALUE(all_agg_com[sales_rep]),
            NOT all_agg_com[sales_rep] IN ExcludedReps || VALUES(all_agg_com[sales_rep]) IN ExcludedReps,
            TRUE
        )
    )
)

[DataDaxGuy]

Firstly, Thank you @Sahir_Maharaj for your kind response. Please read on - 

I'm going to depict what is happening through screenshots. With the version I posted (please examine screenshot 1 labeled 'old1'). You will see that report month 4/1/2024 of the visual is filtered. You see also that p12 (person 12 -  we will say this person is not in the exclusion list which is person 1-4) and p1 (in the exclusion list) are selected. You see that the visual stays static - we retain Feb, Mar, Apr. You also see that the m_static_spiff SUM is 39,454. This is actually only displaying p12's total. p1's amount is not included in that 39,454. This is appropriate behavior - which is interesting, because p1 and p12 work in this situation for both requirements (excluding certain totals depending on whose selected AND staying static for the months displayed regardless of month selected - filter context is properly ignored). Where filter context breaks this - is if I were to filter only p12 (not in the exclusion list) we will see the same result (take my word for it). In this version - if I unselect p12 now and just keep p1 selected (or any number of persons from p1-4 in the ExcludedReps variable) - the visual breaks (and the problem depicted). I will provide this screenshot as well (old2).

Please see screenshot labeled 'new' which is the behavior with the DAX code you suggested as a possible solution. I can assure you that whether p1 is selected alone or with p12. Or anyone not p1-4, or anyone p1-4 - unfortunately, the filter drills to the month when any report month month is selected in the filter pane.

The goals were to
1) Retain the static result of 6 months back (we only see 3 here because the development environment queries that much - prodcution would show all 6).

2) Not add the results of those in the ExcludedReps variable when they are selected with anyone not in the ExcludedReps variable list.

The code I posted works almost fully - except for when you select 1 or more of those from the ExcludedReps variable. Then, it breaks and only shows the month you have selected. It does work if no months or all months are the filter criteria. Although - I need it to work where front line employees might filter other fields that do dynamically change depending on the report_month filter options.

Thanks again in advance.

[DataDaxGuy · Accepted solution]

This was what I went as the final version. This successfully considers to main filter context logic points.

1) Sum people in group 1 when they are selected 1 or more times and don't include their SUM when group 2 people are selected with them one or more times.
2) Ignore report month filter context for all people/groups.

Group 2 people are summed if they are selected one or more times and if no one is selected group 2 people are summed and group 1 people are not.

VAR ExcludedReps = {
    "Person 1", "Person 1", "Person 1", "Person 1"
}

-- Determine if the sales_rep column is filtered to a single value or multiple values
VAR IsIndividualRep = ISFILTERED(all_agg_com[sales_rep])

-- Capture all sales representatives currently selected through the filter pane
VAR SelectedReps = ALLSELECTED(all_agg_com[sales_rep])

-- Check if any sales representatives not in the ExcludedReps list are currently selected
VAR IsAnyNonExcludedRepSelected = 
    NOT ISEMPTY(
        EXCEPT(
            SelectedReps,  -- All selected reps
            ExcludedReps   -- Subtract excluded reps to see if any non-excluded remain
        )
    )

-- Check if any sales representatives in the ExcludedReps list are currently selected
VAR IsAnyExcludedRepSelected = 
    NOT ISEMPTY(
        INTERSECT(
            SelectedReps,  -- All selected reps
            ExcludedReps   -- Intersection with excluded reps to see if any match
        )
    )

-- Determine the return value based on the selection of excluded and non-excluded reps
VAR test = IF(
    IsAnyNonExcludedRepSelected && IsAnyExcludedRepSelected, 
    FALSE(),  -- Return FALSE if selections include both excluded and non-excluded reps
    IF(
        IsAnyExcludedRepSelected,
        TRUE(),  -- Return TRUE only if all selected reps are from the ExcludedReps list
        BLANK()  -- Return BLANK if no reps are selected or if other conditions are met
    )
)

-- Calculate the latest date in the 'report_month_filter' table considering all available data
VAR MaxDate2024 =
    CALCULATE(
        MAX('report_month_filter'[report_month]),
        ALL('report_month_filter'),  -- Remove any existing filters on this table
        YEAR('report_month_filter'[report_month]) = 2024  -- Focus on the year 2024
    )

-- Define the start date as 5 months before the maximum date identified
VAR StartDate = EDATE(MaxDate2024, -5)

-- Calculate the total spiffs for selected ExcludedReps, ignoring the 'report_month' filter
VAR a = CALCULATE(
    SUM(all_agg_com[spiff]),
    FILTER(
        ALL('all_agg_com'[report_month]),  // Ignore any 'report_month' slicers or filters
        'all_agg_com'[report_month] >= StartDate &&
        'all_agg_com'[report_month] <= MaxDate2024)  -- Remove filters from 'report_month'
)

-- Calculate the total spiffs excluding those of ExcludedReps, also ignoring the 'report_month' filter
VAR b = CALCULATE(
    SUM(all_agg_com[spiff]) - SUMX(
        FILTER(
            all_agg_com, 
            [sales_rep] IN ExcludedReps  -- Only consider excluded reps
        ), 
        all_agg_com[spiff]
    ),
    FILTER(
        ALL('all_agg_com'[report_month]),  // Ignore any 'report_month' slicers or filters
        'all_agg_com'[report_month] >= StartDate &&
        'all_agg_com'[report_month] <= MaxDate2024)  -- Remove filters from 'report_month'
)

-- Return the appropriate measure based on whether an individual rep context or a total context is present
RETURN
IF(
    NOT IsIndividualRep,  -- If no individual rep is selected (i.e., aggregating multiple or no selections)
    CALCULATE(
        b,  -- Use the measure excluding ExcludedReps
        ALL(all_agg_com[sales_rep]),  -- Consider all sales reps, removing specific filters
        NOT(all_agg_com[sales_rep] IN ExcludedReps)  -- Ensure excluded reps are not considered
    ),
    IF(
        test,  -- If an individual rep or scenario requires 'a' measure
        a,
        b  -- Otherwise, use 'b' measure
    )
)