How to List All Products Listed Under a Category by Filtering a Product.

[Anonymous]

Hi, 

I am trying to work out a simple logic in Power BI. I have simple model, a product is linked to category with sales number.

If a user filters a product, i want to list out all the products related the category of the "product selected" and the sales amount. I have uploaded an image of the dummy wireframe to illustrate what i aim to do. 

I would appreciate , if you could help. thanks 

thanks 

[jdbuchanan71]

@Omar_Sek 

If the product table was connected it would filter the rows that are returned to the 

SUM ( Sales[Amt] )

 The disconnected table lets us read the user selection then build the list of categories and apply that list as our filter.

[Omar_Sek]

@jdbuchanan71  thanks , i start using the disconnected table for filter but I find some difficulties especially when I have in a page several visualizations, I must make for each visualization these measures and often it is complicated as this example:
I have a list of factories, each factory belongs to a group.
I use a diconnected table for the filters, this table contains the list of factories.
I filter on a factory and I would like to display on map all factories that belong to the same cluster.

[Omar_Sek]

@jdbuchanan71 @v-lili6-msft  

I had the same a problem , this solution works well for me, 

but my question : why we have to use a disconnected table for the filter ? 

[Omar_Sek]

I have a same a problem , the solution work very well but I have a question : why we have to use a disconnected table for the filter ? 

[v-lili6-msft]

hi, @Anonymous 

For your requirement, you couldn't create a relationship between table1 and table2, or you could use a separate table for slicer.

Then create a measure by this formula

Measure = 
VAR _category =
    CALCULATETABLE (
        VALUES ( Table2[Category] ),
        FILTER ( ALL ( Table2 ), Table2[ProductName] IN VALUES ( Table1[ProductName] ) )
    )
RETURN
    CALCULATE (
        SUM ( Table2[Amt] ),
        FILTER ( Table2, Table2[Category] IN _category )
    )

Result:

and here is sample pbix file, please try it.

By the way, if you want to upload the file, You need to upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[Anonymous]

Thanks for the reply.  I have attached the pbix file now. The tables are related, i can see your solution working when the table is not joined but it doesnt work when the tables are with relationship. 

regards

Anbu 

How to List All Products Listed Under a Category by Filtering a Product- DEMO

[jdbuchanan71 · Accepted solution]

@Anonymous 

For @v-lili6-msft  solution to work you need to add a disconnected table of the list of products.

Product Selection = DISTINCT ( 'Product'[ProductName] )

That is the list that the users will pick from.  Then the measure will pick up the product selected, apply that filter to get the category and calc the amount for everything in that category.

Sales Filtered = 
VAR _SelectedCategory =
    CALCULATETABLE (
        VALUES ( Category[Category] ),
        FILTER (
            ALL ( 'Product' ),
            'Product'[ProductName] IN VALUES ( 'Product Selection'[ProductName] )
        )
    )
RETURN
    CALCULATE (
        SUM ( Sales[Amt] ),
        FILTER ( Category, Category[Category] IN _SelectedCategory )
    )

I have updated your sample .pbix with the measures.  https://www.dropbox.com/s/z1e2auo1176z6r5/list_allproductsofacategory.pbix?dl=0

Please mark the post from @v-lili6-msft  as the solution.

[Anonymous]

Thanks  I was eagerly waiting for your reply.  I will try this out now. 🙂 

[Anonymous]

Hey @Anonymous 

As far as I unerstand, you need to create a connection in the model between the tables.

Then the filteration will work.

Please share your PBIX if not resolved.

Cheers!
A

[Anonymous]

Thanks, how do we upload the pbix file here, i cannot see any file upload option. 

regards