AND Slicer condition with multiple many-to-one tables

[dbandrews]

Hi Everyone,

I'm attempting to get a slicer to behave like AND instead of an OR - I've attempted to implement the solutions I've found on here already (https://community.powerbi.com/t5/Desktop/Slicer-with-quot-AND-quot-logic/td-p/592018) but my data model/task is slightly different and I can't get it quite right.

Problem: Given the following data model

I want to have a matrix visual where I can evaluate what fraction of each customer's orders have a certain SuperWidget in the order, while also having certain Widgets present in the order, filtering on multiple Widgets all being present (the AND condition).

What I have so far:

The issue I'm having is highlighted in red in the screenshot above - I want to reduce the denominator by filtering where multiple Widgets are present in the same SaleID. I then want to have the fraction of relevant calls filtered by Widget, where the selected SuperWidget is present.

Where the fractions are calculated with:

Fraction_sales_with_superwidget = 
VAR COUNT_SALES_UN_FILTERED = CALCULATE(DISTINCTCOUNT(SuperWidgetSales[SaleID]), REMOVEFILTERS(SuperWidgetSales[SuperWidget]))
VAR COUNT_SALES_FILTERED = DISTINCTCOUNT(SuperWidgetSales[SaleID])

RETURN DIVIDE(COUNT_SALES_FILTERED, COUNT_SALES_UN_FILTERED)

And my DAX so far to create the AND condition is applied to the Matrix visual as filter:

And is defined as:

Matches_all_selected_widgets = 
VAR _EmptySlicer = 
    CALCULATE(ISFILTERED(WidgetSales[Widget]), ALLSELECTED(WidgetSales[Widget])) = FALSE()

RETURN
IF (
    NOT (_EmptySlicer),
    IF(CALCULATE(DISTINCTCOUNT(WidgetSales[Widget]), ALLSELECTED(WidgetSales[Widget]))
    >= COUNTROWS(ALLSELECTED(WidgetSales[Widget])),
    1
    ),
  1
)

I realize this data model might be sub-optimal for this problem, so if you have any suggestions to improve that to make this easier to solve I'm happy to update. My actual data model has millions of rows on the many side of these relationships so performance is important.

Thanks!

[amitchandak · Accepted solution]

@dbandrews , Based on what I got, refer if my video can help

And for Selected Values, All selected values are present: https://youtu.be/X5T4rIZovHk