Make two disconnected slicers filter a matrix

[Aliceeeee]

I’m trying to make an Availability matrix respond to two slicers that come from the same “Skills” fact table but different attributes (and keep relationships single‑direction).

Visual setup

• Matrix rows: FullName_All[FirstName] (dimension)
• Matrix values: Hours (skills aware) measure
• Slicers:
  • Certifications[Certification] (disconnected list from Skills where Attribute = "Certifications")
  • Screening[Screening] (disconnected list from Skills where Attribute = "Screening")

Model (relevant parts)

• FullName_All is the conformed people dimension.
• Single‑direction relationships from FullName_All → Availability sheet, Advanced, Skills, WIP Report, Query.

Calculated tables for slicers:

Certifications :=
DISTINCT(
    SELECTCOLUMNS(
        FILTER(Skills, TRIM(Skills[Attribute]) = "Certifications"),
        "Certification", Skills[Value]
    )
)

Screening :=
DISTINCT(
    SELECTCOLUMNS(
        FILTER(Skills, TRIM(Skills[Attribute]) = "Screening"),
        "Screening", Skills[Value]
    )
)

Working measures (each works on its own)

• Certifications only:

Hours (skills aware) =
VAR HasCertFilter = ISFILTERED(Certifications[Certification])
RETURN IF(
    HasCertFilter,
    CALCULATE(
        SUM('Availability sheet'[Hours]),
        CROSSFILTER(FullName_All[FullNameKey], Advanced[Full Name], BOTH),
        CROSSFILTER(FullName_All[FullNameKey], Skills[Full Name], BOTH),
        KEEPFILTERS(FILTER(Skills, TRIM(Skills[Attribute]) = "Certifications")),
        TREATAS(VALUES(Certifications[Certification]), Skills[Value])
    ),
    CALCULATE(
        SUM('Availability sheet'[Hours]),
        CROSSFILTER(FullName_All[FullNameKey], Advanced[Full Name], BOTH),
        CROSSFILTER(FullName_All[FullNameKey], Skills[Full Name], BOTH)
    )
)

• Screening only (same pattern, works):

Hours (skills aware — screening only) =
VAR HasScreening = ISFILTERED(Screening[Screening])
RETURN IF(
    HasScreening,
    CALCULATE(
        SUM('Availability sheet'[Hours]),
        CROSSFILTER(FullName_All[FullNameKey], Advanced[Full Name], BOTH),
        CROSSFILTER(FullName_All[FullNameKey], Skills[Full Name], BOTH),
        KEEPFILTERS(FILTER(Skills, TRIM(Skills[Attribute]) = "Screening")),
        TREATAS(VALUES(Screening[Screening]), Skills[Value])
    ),
    CALCULATE(
        SUM('Availability sheet'[Hours]),
        CROSSFILTER(FullName_All[FullNameKey], Advanced[Full Name], BOTH),
        CROSSFILTER(FullName_All[FullNameKey], Skills[Full Name], BOTH)
    )
)

However, I can't seem to figure out how I can combine these into a single measure.

Constraint to keep

• Expertise slicer is built from Advanced (SkillCategory_Slicer and SkillLevel_Slicer).
• The hours measure must continue to respect Expertise via:

CROSSFILTER(FullName_All[FullNameKey], Advanced[Full Name], BOTH)

• The matrix has a visual‑level filter which uses FullName_All to hide names when only Skill Category is selected without Skill Level.

Question

• What is the correct, minimal DAX pattern to combine both disconnected slicers (Certifications + Screening) so the matrix shows:
  • certifications only,
  • screening only,
  • the intersection when both are selected, while still respecting the Expertise slicer?

Thank you.

[v-prasare]

Hi @Aliceeeee,

we are checking in to see did the solution I shared work for you? Or were you able to prepare and share a sample data file & expected outcome so we can better understand the scenario and help further?

Having that sample data (even with sensitive info removed) really helps pinpoint the exact behavior and provide the best solution.

Thanks,

Prashanth

[v-prasare]

Hi @Aliceeeee,

we are checking in to see did the solution I shared work for you? Or were you able to prepare and share a sample data file & expected outcome so we can better understand the scenario and help further?

Having that sample data (even with sensitive info removed) really helps pinpoint the exact behavior and provide the best solution.

Thanks,

Prashanth

[cengizhanarslan]

Could you try the formula below:

Hours (skills aware - both slicers) :=
VAR CertPeople =
    IF (
        ISFILTERED ( Certifications[Certification] ),
        CALCULATETABLE (
            VALUES ( Skills[FullNameKey] ),                 -- use the person key that exists in Skills
            Skills[Attribute] = "Certifications",
            TREATAS ( VALUES ( Certifications[Certification] ), Skills[Value] )
        ),
        VALUES ( FullName_All[FullNameKey] )                 -- no cert filter => don't restrict
    )

VAR ScreenPeople =
    IF (
        ISFILTERED ( Screening[Screening] ),
        CALCULATETABLE (
            VALUES ( Skills[FullNameKey] ),
            Skills[Attribute] = "Screening",
            TREATAS ( VALUES ( Screening[Screening] ), Skills[Value] )
        ),
        VALUES ( FullName_All[FullNameKey] )
    )

VAR PeopleToKeep =
    INTERSECT ( CertPeople, ScreenPeople )

RETURN
CALCULATE (
    SUM ( 'Availability sheet'[Hours] ),

    -- keep your expertise behavior (Advanced slicers)
    CROSSFILTER ( FullName_All[FullNameKey], Advanced[Full Name], BOTH ),

    -- apply the final people set to the dimension (so single-direction model stays OK)
    TREATAS ( PeopleToKeep, FullName_All[FullNameKey] )
)

[Aliceeeee]

I tried it, and it shows this error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[cengizhanarslan]

Is it possible for you to share a .pbix example?

[Aliceeeee]

I looked into it, but it's quite big with lots of sensitive data, and recreating it using synthetic data would be too time consuming. I appreciate your question however.

I see that you also replied to my earlier question on a different topic here. This new question is a follow up to the earlier one. Previously you suggested using TREATAS and below code indeed allows me to filter based on certification. However, now I want to add yet another slicer with data from a different set of values (from the same Skills table). So effectively my question is how do I use TREATAS for multiple slicers, not just one (as that's seems to be the challenge I am trying to solve).

Hours (skills aware) =
VAR HasCertFilter = ISFILTERED(Certifications[Certification])
RETURN IF(
    HasCertFilter,
    CALCULATE(
        SUM('Availability sheet'[Hours]),
        CROSSFILTER(FullName_All[FullNameKey], Advanced[Full Name], BOTH),
        CROSSFILTER(FullName_All[FullNameKey], Skills[Full Name], BOTH),
        KEEPFILTERS(FILTER(Skills, TRIM(Skills[Attribute]) = "Certifications")),
        TREATAS(VALUES(Certifications[Certification]), Skills[Value])
    ),
    CALCULATE(
        SUM('Availability sheet'[Hours]),
        CROSSFILTER(FullName_All[FullNameKey], Advanced[Full Name], BOTH),
        CROSSFILTER(FullName_All[FullNameKey], Skills[Full Name], BOTH)
    )
)

[v-prasare · Accepted solution]

Hi @Aliceeeee,

Please convert each slicer selection into its corresponding dimension key set by using TREATAS, making sure it produces a single column table that matches 'Dim People'[FullNameKey]. If a slicer table contains multiple columns, reduce it to only the required key column.

 Next, apply INTERSECT() across these key sets to retain only the entities that are common to all slicer selections, ensuring true AND logic. Finally, apply the intersected key set back to the dimension using a final TREATAS so that measures are filtered correctly. This approach preserves single direction relationships, keeps slicers independent, allows other slicers (such as Expertise) to behave as expected, and ensures accurate, predictable results.

Please try below DAX measures:

CertPeopleKeys := VAR CertKeysFromSlicer =
    CALCULATETABLE (
        VALUES ( 'Dim People'[FullNameKey] ),
        TREATAS ( VALUES ( 'Certifications'[PersonKey] ), 'Dim People'[FullNameKey] )
    )
RETURN
    IF ( ISFILTERED ( 'Certifications'[PersonKey] ), CertKeysFromSlicer, VALUES ( 'Dim People'[FullNameKey] ) )





ScreenPeopleKeys := VAR ScreenKeysFromSlicer =
    CALCULATETABLE (
        VALUES ( 'Dim People'[FullNameKey] ),
        TREATAS ( VALUES ( 'Screening'[PersonKey] ), 'Dim People'[FullNameKey] )
    )
RETURN
    IF ( ISFILTERED ( 'Screening'[PersonKey] ), ScreenKeysFromSlicer, VALUES ( 'Dim People'[FullNameKey] ) )

If above solution does not works as @cengizhanarslan suggested please share sample pbix file so that we will reproduce from our end.

Thanks,

Prashanth