Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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?
Here is a screenshot of Performance analyzer - It looks like the DAX code is slow.
@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() ))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |