Forum Discussion

Aliceeeee's avatar
Aliceeeee
Frequent Visitor
8 months ago
Solved

Make two disconnected slicers filter a matrix

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.

  • 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

     

7 Replies

  • 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's avatar
      Aliceeeee
      Frequent Visitor

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

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support

    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's avatar
    v-prasare
    Icon for Community Support rankCommunity Support

    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