Forum Discussion

deepuaits's avatar
deepuaits
Frequent Visitor
1 year ago
Solved

Mixed Filters in Table Visual (Selected School vs Cohort vs Overall Stats)

Measure Requirements:

  1. Selected School Value:

    • This value comes from a slicer that selects a school.

    • The slicer uses a duplicate School table (not related to the fact table) due to RLS (Row-Level Security).

    • I use this table to isolate what the RLS user can select, and it shouldn’t affect other measures.

  2. Cohort Average:

    • Comes from a separate slicer (different cohort of schools).

    • Also based on a disconnected table or logic.

    • Should calculate the average only for the selected schools in the cohort slicer.

  3. Other statistics (Average, Min, Max, Quartiles, Response Count):

    • These should not be affected by the slicers.

    • But should respect other visual-level filters (e.g. Category).

To show all values in one table visual, filtered by Category (if needed), but:

  • Selected School Value is based on RLS-safe slicer

  • Cohort Average is based on separate cohort slicer

  • All other stats ignore slicers, but keep Category and other report/page/visual filters

Example Table :

Can someone help me with the right DAX expressions for each of the above (especially the TREATAS usage for Selected School and Cohort)? Also, let me know if there’s a better approach to structure this logic or model.

 

 

Thanks

Pradeep

data protection
dataflow
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    1 year ago

    Hello deepuaits,

     

    Can you please try this approach:

     

    Selected School Value :=
CALCULATE(
    SUM('FactTable'[Value]),
    TREATAS(
        VALUES('SelectedSchoolTable'[SchoolName]), 
        'FactTable'[SchoolName]
    )
)

    Cohort Average :=
CALCULATE(
    AVERAGEX(
        VALUES('FactTable'[SchoolName]),
        CALCULATE(SUM('FactTable'[Value]))
    ),
    TREATAS(
        VALUES('CohortSelector'[SchoolName]), 
        'FactTable'[SchoolName]
    )
)

    Average :=
CALCULATE(
    AVERAGE('FactTable'[Value]),
    REMOVEFILTERS('SelectedSchoolTable'),
    REMOVEFILTERS('CohortSelector')
)

Minimum :=
CALCULATE(
    MIN('FactTable'[Value]),
    REMOVEFILTERS('SelectedSchoolTable'),
    REMOVEFILTERS('CohortSelector')
)

First Quartile :=
PERCENTILEX.INC(
    FILTER(
        ALLSELECTED('FactTable'), 
        NOT ISBLANK('FactTable'[Value])
    ),
    'FactTable'[Value], 
    0.25
)

Median :=
PERCENTILEX.INC(
    FILTER(
        ALLSELECTED('FactTable'), 
        NOT ISBLANK('FactTable'[Value])
    ),
    'FactTable'[Value], 
    0.5
)

Third Quartile :=
PERCENTILEX.INC(
    FILTER(
        ALLSELECTED('FactTable'), 
        NOT ISBLANK('FactTable'[Value])
    ),
    'FactTable'[Value], 
    0.75
)

Max :=
CALCULATE(
    MAX('FactTable'[Value]),
    REMOVEFILTERS('SelectedSchoolTable'),
    REMOVEFILTERS('CohortSelector')
)

Response Count :=
CALCULATE(
    DISTINCTCOUNT('FactTable'[SchoolName]), 
    REMOVEFILTERS('SelectedSchoolTable'),
    REMOVEFILTERS('CohortSelector')
)

     

    Hope this helps.

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    1 year ago

    Hello deepuaits,

     

    Can you please try this approach:

     

    Selected School Value :=
CALCULATE(
    SUM('FactTable'[Value]),
    TREATAS(
        VALUES('SelectedSchoolTable'[SchoolName]), 
        'FactTable'[SchoolName]
    )
)

    Cohort Average :=
CALCULATE(
    AVERAGEX(
        VALUES('FactTable'[SchoolName]),
        CALCULATE(SUM('FactTable'[Value]))
    ),
    TREATAS(
        VALUES('CohortSelector'[SchoolName]), 
        'FactTable'[SchoolName]
    )
)

    Average :=
CALCULATE(
    AVERAGE('FactTable'[Value]),
    REMOVEFILTERS('SelectedSchoolTable'),
    REMOVEFILTERS('CohortSelector')
)

Minimum :=
CALCULATE(
    MIN('FactTable'[Value]),
    REMOVEFILTERS('SelectedSchoolTable'),
    REMOVEFILTERS('CohortSelector')
)

First Quartile :=
PERCENTILEX.INC(
    FILTER(
        ALLSELECTED('FactTable'), 
        NOT ISBLANK('FactTable'[Value])
    ),
    'FactTable'[Value], 
    0.25
)

Median :=
PERCENTILEX.INC(
    FILTER(
        ALLSELECTED('FactTable'), 
        NOT ISBLANK('FactTable'[Value])
    ),
    'FactTable'[Value], 
    0.5
)

Third Quartile :=
PERCENTILEX.INC(
    FILTER(
        ALLSELECTED('FactTable'), 
        NOT ISBLANK('FactTable'[Value])
    ),
    'FactTable'[Value], 
    0.75
)

Max :=
CALCULATE(
    MAX('FactTable'[Value]),
    REMOVEFILTERS('SelectedSchoolTable'),
    REMOVEFILTERS('CohortSelector')
)

Response Count :=
CALCULATE(
    DISTINCTCOUNT('FactTable'[SchoolName]), 
    REMOVEFILTERS('SelectedSchoolTable'),
    REMOVEFILTERS('CohortSelector')
)

     

    Hope this helps.