PowerBI In Slicer A, Not In Slicer B

[apalmer1]

I am working on a PowerBI project and having trouble filtering my data—ultimately, I think the solution is a calculated table based on the First Item and Second Item slicers.

Below is a sample PBI file I put together (I cannot figure out how to attach the PBIX file).  The CustomerSales table houses all the data.  ItemA and ItemB are calculated tables holding only the distinct ItemNames to be used in slicers.

The end result is to see all customer who purchased X items and DID NOT purchase Y items.

I have a slicer based off the ItemA table that give me all customers who purchased X items.  However, I am struggling with the second part to show which customers DID NOT purchase Y items.

In the attached example, if I selected Widget A from First Item, it will return all customers who purchased that Widget.  From there, I would like to be able to select Widget C from the Second Item slicer with an expected output of the four Kyle lines.

Also, I’m not sure if this makes a difference, but ultimately this will be driven by a SSAS model.

[v-easonf-msft · Accepted solution]

Hi, @apalmer1 

You can try to disconnect the relationship between 'ItemA/B' table and  'CustomerSales' table in your original SSAS model, and then add the following filter measure to the visual filter pane.

filter1 = 
IF (
    ISFILTERED ( ItemA[ItemName] ),
    IF (
        SELECTEDVALUE ( CustomerSales[ItemName] ) IN VALUES ( ItemA[ItemName] ),
        1,
        0
    ),
    1
)

filter2 = 
IF (
    ISFILTERED ( ItemB[ItemName] ),
    IF (
        SELECTEDVALUE ( CustomerSales[ItemName] ) IN VALUES ( ItemB[ItemName] ),
        0,
        1
    ),
    1
)

Visual filter = 
VAR f1 =
    MAXX (
        ALLEXCEPT ( CustomerSales, CustomerSales[CustomerName] ),
        ItemA[filter1]
    )
VAR f2 =
    MINX (
        ALLEXCEPT ( CustomerSales, CustomerSales[CustomerName] ),
        ItemA[filter2]
    )
RETURN
    IF ( f1 = 1 && f2 <> 0, 1, 0 )

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[apalmer1]

Hi! Thanks for the reply on this. I was able to reproduce your solution in PBI, but it doesn't seem to be scaling with larger datasets.  I created a SSAS model with only 600k records (the whole dataset is 16 million lines), and when using this solution it will run for about 30 minutes before returning the desired results.

Do you have any other ideas this might help?

[apalmer1]

Here is a screenshot of Performance analyzer - It looks like the DAX code is slow. 

[amitchandak]

@apalmer1 , based on what I got, Both slicer need to be independent. Then  the selection in measure

Item slicer 1 = calculate(distinctcount(Table[Item ID]), filter(Table,Table[ID] in allselected(slicer1[Item ID]))

Item slicer 2 = calculate(distinctcount(Table[Item ID]), filter(Table,Table[ID] in allselected(slicer2[Item ID])))

1 and 2 count item purchased from 1 and 2

Measure =

var _cnt1 = countx(allselected(slicer1) , slicer1[Item ID])

var _cnt2 = countx(allselected(slicer2) , slicer2[Item ID])

//do only if need all item

var _3 =  if([Item slicer 1] =_cnt,1, blank())

var _4 = if([Item slicer 2] =_cnt,2, blank()) 

return

countx(values(Table[Customer]), if(not(isblank(_3)) && isblank(_4) , [Customer], blank() ))