Multi-Select Slicer via AND instead of OR logic

[markb34]

Trying to create DAX code to when I select two or more components from a Slicer, it returns the only the kits containing those components. I am only seeing examples where sums are being used. I dont need to sum any dollars just return the Unique items containing the components selected from the slicer.

Example

 Im selecting from a SqlView the Component description from an Item Table to filter a Bills of Material table where it would give me the master item number the component belongs in.   But the native OR logic is being used and i get ALL the Bills of Material Master numbers instead of only the Master numbers that contain both items selected. 

[Anonymous]

Hi @markb34,

Thank you for reaching out in Microsoft Community Forum.

Power BI applies OR logic when multiple slicer values are selected. To apply AND logic, you can use the following DAX measure:

KitsContainingAllSelectedComponents :=
VAR SelectedComponents =
VALUES ( ICITEM[Desc] )

VAR NumSelected =
COUNTROWS ( SelectedComponents )

RETURN
CALCULATE (
COUNTROWS ( vBOMSCosted ),
FILTER (
VALUES ( vBOMSCosted[MasterItem] ),
CALCULATE (
COUNTROWS (
INTERSECT (
SelectedComponents,
VALUES ( vBOMSCosted[comp_Desc] )
)
)
) = NumSelected
)
)

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[markb34]

Thank you for the response but unfortunaltly the measure is still giving me blank results.  But to be honest not sure where to insert the Measure. I put it in the filter for the grid where my results are supposed to show.

[Anonymous]

Hi @markb34,

Thank you for reaching out in Microsoft Community Forum.

As per the your reply, the issue is likely one of the following:

1.applying the measure directly as a visual-level filter may not return expected results without a proper return value (like 1/0 or TRUE/FALSE).

2.The relationship between ICITEM[Desc] and vBOMSCosted[comp_Desc] might not be properly modeled, which is crucial for INTERSECT to work.

You're correct that Power BI applies OR logic by default when multiple values are selected in a slicer. To return only the Kits (MasterItems) that contain all selected components (i.e., apply AND logic), you can use the following DAX measure:

ShowKitsWithAllSelectedComponents :=
VAR SelectedComponents = VALUES(ICITEM[Desc])
VAR NumSelected = COUNTROWS(SelectedComponents)
VAR MatchingComponents =
CALCULATE (
COUNTROWS (
INTERSECT (
SelectedComponents,
VALUES(vBOMSCosted[comp_Desc])
)
)
)
RETURN
MatchingComponents = NumSelected

Please continue using Microsoft Community Forum.

If this post helped resolve your issue, kindly consider marking it as "Accept as Solution" and give it a 'Kudos' to help others find it more easily.

Regards,
Pavan

[markb34 · Accepted solution]

Thank you for all your help.. I was able to figure this out and had to add a card with the Measure to get the results.  My table would not filter with the measure in it. I also modified the results to only show the #1 counts from the meaure 

ShowKitsWithAllSelectedComponents =

VAR SelectedComponents = VALUES(ICITEM[Desc])

VAR NumSelected = COUNTROWS(SelectedComponents)

VAR MatchingComponents =

CALCULATE (

COUNTROWS (

INTERSECT (

SelectedComponents,

VALUES(vBOMSCosted[comp_Desc])

)

)

)

RETURN

IF (MatchingComponents = NumSelected, 1, BLANK())

This is what my result looks like with the newly added measure inserted into a card 

[markb34]

This is the Dax code I have so far.

NumOfComponentsSelected =

// number of components selected for the cross reference

//Count the number of criteria (components) selected

VAR selectedcomponents = VALUES(ICITEM[DESC])

VAR NumofSelectedComponents= COUNTROWS(selectedcomponents)

//count number of selected ccomponents for the cross reference

VAR Result = COUNTROWS(

    FILTER(

        GROUPBY(

            FILTER(vBOMSCosted, RELATED(ICITEM[DESC]) IN selectedcomponents)),

            vBOMSCosted[BOMComponents]

            COUNTX(CURRENTGROUP(), [DESC]

[markb34]

I have a slicer with mulit selection pullng from one SQLview field called ICITEM[Desc} that filters a table consisting of  Bills of Material for kits we make. But when I select two components it gives me all the Kits for each selection. I only want the Kits that contain those two components I selected. I have been trying for 2 weeks to come up with the dax code to accomplish this. I found a few videos and see the Fabric page Solved: Filtering a Multi-Select Slicer via AND instead of... - Microsoft Fabric Community but I can not figure this out.. Would you be able to asssist?

ICITEM is the first View ICITEM[desc]

vBomsCosted is second view vBomsCosted[comp_Desc]