Dynamic Selection - Multiple Categories

[Anonymous]

Hi All,

I would like to ask some help with the below.

I have Sales table connected with Customer table and Product table. Each customer in the Customer table is categorised 3 different ways (based on Product Divisions). I would like to create a "dynamic selection" or a measure (???) that would help me with the following.

If a certain Product Division is selected all visuals/tables/matrix in my PBI file should pick up the categories associated with that particular Division.

For example: 

Customer name	Customer Country	Customer group by HW dept	Customer group by SW dept	Customer group by Other
Contoso Ltd.	GB	Medium	Tester	+100k
Van Arsdel Ltd.	NL	Medium	End-user	50-100k
Fabrikam Inc.	US	Low	Supplier	<20k
Microsoft	US	High	End-user	+100k

HW Team categories for customers: Low/Medium/High

Other Team categories for customers: +100k/50-100k/<20k

Software Team categories for customers: Tester/End-User/Supplier

So essentially each customer is classified by all Divisions somehow. I also have other information about the customers, such as Country that I need to use for other visuals.

Let's say that user picks (or limited to see) HW products from the Product table then they should see HW Team Categories only. Also, I would like to use that selection in a slicer, bar chart, etc as their "Category"

When NO Product division is selected then this "Category" should be just "NA" or empty - for example if someone is responsible for a Customer (as in Key Account or cross-Divisional sales) not a Product Division there is no need to split Customers by Category. Hope this make sense.

Now, I can more or less solve the issue with Bookmarks (for each Division), however, Bookmarks do not work too well with different security levels/Roles. (or at least based on my knowledge).

Bookmarking is a solution, but I guess there is a more elegant way of doing this. I have a feeling that maybe with some conditional columns or a slightly different Data Model there would be a more efficient solution for this. This solution is also in my example file.

pbix file is attached

Thanks heaps in advance!

rszirmai

[danextian]

Hi @Anonymous 

If your data isn't too big, you may try this approach:

https://community.powerbi.com/t5/Desktop/Dynamic-Category-Using-a-Slicer/td-p/216971

[Anonymous]

@danextian 

Thanks a lot for your reply!

If I understand correctly, your FACT table is actually a flat table that already contains all the details from DIMENSION tables. That's what you index and then use the index to create the artificial dynamic slicer. Really nice approach!

Unfortunately, in my case it will not work, because:

• I have lots of records in my fact table (invoice line items)
• with my star schema there is no way to replicate what you did (it would be in a sense circular reference or kind of)

My main problem is that the Divisional data must come from the Products table and have to use the selected value from there to pick up my categories from Customer table.

Obviously, I would be able to eliminate the need of this difficult logic by simply creating 3 PBI dashboards, but I would prefer to have my data in 1 place.

Thanks

rszirmai