DAX - Calculate average for rows not included in slicer selections

[chatals]

I'm working on a clinical trials study. I have a column "Symptom Progression Rate (SPR)" which is a numeric value that describes the speed at which an illness is progressing. For study participants, I'm trying to compare an average of that value, when filtered by a one or more demographic slicers such as gender, age, and ethnicity as well as clinical slicers that includes medications and nutrients, to the average SPR for participants who are not included in the slicer selections.

For example, out of 1,500 study participants, if I select 400 male participants between the age of 40-50 that are taking a particular drug, I want to calculate the average SPR for the 1,100 that are not selected by any of the slicers.

Model

DIM
Participant Table
-Participant ID
-Age
-Gender
-Ethnicity

(1:1)

Slope Table
-Participant ID
-SPR

(1:Many)
Drug Table
-ParticipantID
-Name of Drug

(1:Many)
Nutrient Table
-Participant ID
-Nutrient Name

[v-nuoc-msft · Accepted solution]

Hi @chatals 

For your question, here is the method I provided:

“Participants”

“Slope”

“Drugs”

“Nutrients”

I suggest that you modify the relationship between them as follows:

Enter the power query interface to merge a new table for multiple slicers:

Don't create any relationships.

Create measures.

Average_SPR_Filtered = 
var _minage = MIN('Merge Table'[Age])
var _maxage = MAX('Merge Table'[Age])
var _gender = SELECTEDVALUE('Merge Table'[Gender])
var _ethnicity = SELECTEDVALUE('Merge Table'[Ethnicity])
var _drug = SELECTEDVALUE('Merge Table'[Drugs.Name of Drug])
RETURN
AVERAGEX(
    FILTER(
        'Participants',
        'Participants'[Gender] = _gender 
        &&
        'Participants'[Age] >= _minage && 'Participants'[Age] <= _maxage 
        &&
        'Participants'[Ethnicity] = _ethnicity
        &&
        RELATED('Drugs'[Name of Drug]) = _drug
    ),
    RELATED(Slope[SPR])
)

Average_SPR_Non_Filtered = 
var _minage = MIN('Merge Table'[Age])
var _maxage = MAX('Merge Table'[Age])
var _gender = SELECTEDVALUE('Merge Table'[Gender])
var _ethnicity = SELECTEDVALUE('Merge Table'[Ethnicity])
var _drug = SELECTEDVALUE('Merge Table'[Drugs.Name of Drug])
RETURN
AVERAGEX(
    FILTER(
        'Participants',     
        'Participants'[Gender] = _gender 
        &&
        ('Participants'[Age] < _minage || 'Participants'[Age] > _maxage)
        &&
        'Participants'[Ethnicity] <> _ethnicity
        &&
        RELATED('Drugs'[Name of Drug]) <> _drug   
        
    ),
    RELATED(Slope[SPR])
)

Here is the result.

Regards,

Nono Chen

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