Need help with selecting Items from a Filter, and populating all items in the same category ?

[PBIUWO]

Hi,

Question: 

I want to be able to click on an Item or multiple items in the Slicer Filter, and in the table visual, show the selected item and it's related items (same category).

*I created a measure already (show below) but it is not efficient and causes the PowerBI to crash because of the size of the data. 

Background: 

I have a Power BI Report with Data of Sales Information of Items.

1. The Table has Item Code, Item Category, and Sales Information by Month/Year. Sales Table.  

Table name: Sales

2. I have created another table (Unique Item Values) with the item codes in the Sales Table, and have no relationship. 

Table name: Unique Item Values

In the page,  the Item Code column from Unique Item Values, is the Slicer filter.

The Sales Table, is the values in the Table Visual. and the measure below is placed in the Filter panel in the visual with anything greater than 1 as the rule. 

I have created a measure to do this: 

Related Items =

VAR Related_Category =

CALCULATETABLE (

VALUES ('Sales'[Category]),

FILTER ( ALL ('Sales'),'Sales'[Item Code] IN VALUES ( 'Unique Item Value'[Item Code] ) ))

RETURN

Calculate(DISTINCTCOUNT('Sales'[Item Code]),FILTER('Sales','Sales'[Category] IN Related_Category ))

*The measure is very inefficient, and makes the PBI crash. How can I fix it? 

[parry2k]

@PBIUWO why not you create the relationship between these two tables since you have a distinct item value table and then you don't need any complicated dax, it will auto-filter your table visual.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[PBIUWO]

@parry2k 

So if I do that, how can I select item(s) in the Slicer, and it showing the related items (items with the same category) in the table?

[v-yingjl · Accepted solution]

Hi @PBIUWO ,

After you create relationships between tables based on the item code, visuals about the Sales table will be filtered by the slicer automatically:

Refer:

1. Create and manage relationships in Power BI Desktop 
2. Slicers in Power BI 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.