Forum Discussion
Make two disconnected slicers filter a matrix
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
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)
)
)
Community SupportHi 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