Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thanks heaps in advance!
rszirmai
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
Proud to be a Super User!
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |