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,
need some help with the relationship between four tables and slicers.
Following situation: I have a large approx table with so following rows:
ApproxTable:
date | hour | clientID | appId | contentID | reach | person | map | street |
2019-10-12 | 1 | ABC | 1 | 43 | 784 | Mike | true | Londonstreet |
2019-10-12 | 2 | ABC | 2 | 31 | 102 | Tim | true | Berlinstreet |
2019-10-13 | 1 | ABC | 1 | 9 | 89 | Paul | false | Londonstreet |
2019-10-13 | 3 | XYZ | 3 | 1 | 278 | Tim | false | Parisstreet |
I have a content client Mapping table
MappingTable:
clientID | appID | contentID |
ABC | 1 | 43 |
ABC | 1 | 9 |
ABC | 2 | 31 |
ABC | 4 | 39 |
XYZ | 3 | 1 |
XYZ | 5 | 27 |
And two tables for content and app informations:
ContentTable:
contentID | contentName | contentImage |
1 | Content1 | link zu ContentImage1 |
9 | Content9 | link zu ContentImage9 |
27 | Content27 | link zu ContentImage27 |
31 | Content31 | link zu ContentImage31 |
39 | Content39 | link zu ContentImage39 |
43 | Content43 | link zu ContentImage43 |
AppTable:
appID | appName | appImage |
1 | App1 | link zu AppImage1 |
2 | App2 | link zu AppImage2 |
3 | App3 | link zu AppImage3 |
4 | App4 | link zu AppImage4 |
5 | App5 | link zu AppImage5 |
Now i want to slice the approx table information with three slicers: 1. clientID 2. appID 3. contentID
And if ClientID selected over slicer 1. => Slice2 and 3 lists only the available appIDs and contentIDs which are available in the approx table.
I tried every combination of relationships between this 4 tables. Created new tables with unique ids and try to related to the other. But nothing works for me. I build the following relationship model:
approx n:1 contentInf (over contentID) (direction content -> approx)
approx n:1 appInf (over appID) (direction app -> approx))
approx n:n mapping (over clientID) (direction mapping -> approx))
In this case i can slice the approx information but the slicers has no relationship between each other.
Would be thankful for helping answers.
Bg
Power BI can make you pull your hair out sometimes.
I copied your samples into excel and then loaded them in power bi
i did not use your Map table since the relationships in power bi allow me to make the same mapping
i then created a report with 3 slicers ( the slicers are just table elements with one field from approx table dragged onto them) and one table... i wrote no code
When you select an appid, the other 2 slicers show the clientid and contentid values available for that app id. and the full table display shows all the candidate rows
if you select one of the 2 content ids available, the full table will only show that content row.
This all happens because if the relationships and how power bi hooks up the graphical elements. It gets me every time... i ALWAYS start out trying to do way to much work in code to make things work..... 🙂
I think this is what you were looking for...if not let me know and we'll have another go at it.
Help when you know. Ask when you don't!
Hi thanks,
thats good but i want that the slicer shows the app name, client name and content name, not the ids. So i have to merge the approx with the content and app table over the contentName, appName and put this new approx rows to the slicer?
Edit:
I tried your relationship model. And i have to clients: ABC and XYZ. ABC has only the apps 1,2,4 and XYZ: 3,5. So if i select abc the slicer has to filter the apps which depends to the client.
Client slicer: ABC
App slicers hast to show only:: 1,2,4
Content slicers has to show only: 9, 31, 39, 43
If i select
Client: ABC
App: 1
Content slivers hast to show only: 9,43
BG
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 |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |