Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Help with shaping data

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!

Need Help

3 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        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