Help with shaping data

[Anonymous]

I'm attaching a pbix file where everything is setup for help. Basically, I need a slicer to group data and split them by a column. The data has 3 columns:

Shopper

Product
IsPurchase

A user needs to select a product from a slicer, and I need a table to show products and count of shoppers that have IsPurchase=1 for the selected product in the slicer, but IsPurchase=0 for the product in the row. 

If you open the pbix file and look at the data, you will see better what I need help with 🙂

Here is the file: PBIX

THANKS!

[v-huizhn-msft]

Hi @Anonymous,

A slicer narrows the portion of the dataset shown in the other visualizations on the page. Slicers are used to filter the rows in table. When you select the product A, it will only return the result based on product A rows, while we are not able to get product B and C. Please review the following screenshot.

Best Regards,
Angelia

[Anonymous]

Thanks for the reply Angelia (@v-huizhn-msft). My question is how can I filter shoppers that have at least one row with the selected product from the slicer. It doesn't have to be slicer, but any kind of thing that will help me achieve this (DAX perhaps to group by?)

Looking forward to your response.

[v-huizhn-msft]

Hi @Anonymous,

You'd better create a measure use the following formula.

Measure = CALCULATE(COUNTA(Sheet1[Product]),FILTER(Sheet1,Sheet1[IsPurchase]=1))

Then create a table, select the product and meaure as row level. You will get that how many the shopper purchase the product.


While in Power BI desktop, you can't get ProductB and ProductC when you filter product A .

Best Regards,
Angelia