4 tables with 3 slicer in relationship | dependence problem

[MarcS]

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

[kentyler]

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.

[MarcS]

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