Forum Discussion

apalmer1's avatar
apalmer1
Frequent Visitor
4 years ago
Solved

PowerBI In Slicer A, Not In Slicer B

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.

 

 

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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() ))

     

     

     

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    4 years ago

    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's avatar
      apalmer1
      Frequent Visitor
      4 years ago

      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's avatar
        apalmer1
        Frequent Visitor
        4 years ago

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