How to avoid Ambiguity and join 4 different tables that we need for report?

[Anonymous]

Hi Community,

I am not sure on how to resolve the issue I am facing with a data model . I have 4 tables which are joined as shown in below screenshot. (People,Date, INC&WO and dcmsAgent).

There is a table visual in report which uses different fields from these 4 tables. Also I have 2 Slicers called Date and User ID(LoginID in dcmsagent , corporate ID in INC&WO tables) . With the current data model i have shown below  , I am able to filter my data in table visual by User ID as all the required tables are connected . However, When i apply  a filter on Date slicer, only data belongs to INC&WO changes , but not dcmsagent because Date table and dcmsagent tables are not connected. When i am trying to connect , i receive below error . 

Not sure on how to resolve this issue.Kindly assist. 

G Venkatesh 

[v-easonf-msft]

Hi,  @Anonymous 

Based on which fields do you want to join table"ALL Date" and table "dCmsAgent"?

Which field is used as User ID in the slicer ,LoginID in dcmsagent or  corporate ID in INC&WO tables?

You can try to change the cross filter direction to single, and establish the relationship as shown in the figure below:

If it doesn't work ,please share a sample pbix file for further research.

Best Regards,
Community Support Team _ Eason

[daxer-almighty]

@Anonymous 

Straight away I can tell you that this design is flawed. Big time. You should abandon it and create a model that follows Best Practices. Especially the many-to-many with bi-directional filter is a NO-NO. If you keep creating such models, you'll be having no end of issues. What's more and worse, you will not even be aware that such a model calculates wrong figures. And you won't be even able to explain how the figures you see are calculated.

The proper design is a star schema. Bi-dir relationships are to be used with great care and only in very specific scenarios. The same is true for the so-called many-to-many relationship (which in fact is not). This latter has been designed to solve one specific issue: that of granularity. So, if you throw these techniques at your model because you think that they're cool and speed up development, or you just think that it'll be OK... well, don't be surprised that in no time you'll be producing rubbish that nobody will be able to explain in a month or so (including you).

That's a gentle warning from an old-timer that has built cubes for very big multinational businesses and has studied DAX under the supervision of The Italians.

[Anonymous]

Hi @daxer-almighty 

Your warning is in such a way that I  have blown up the data model so badly. Thats a good banter though . 😀

Okay . I am sure you are talking about Marco russo and Alberto Ferrari supervision . However,can you some suggest videos that i have to quickly look at and resolve the issue. I am lost in this world of Power BI and dont know which one i have to look at . 

Thanks in advance ! 

G Venkatesh 

[daxer-almighty]

Hi there.

I'd first say you can read this: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

And then look at what's in the TOC on the left-hand side. There's a lot of read you could do with much benefit.

Secondly, there are very good courses on www.sqlbi.com. Yes, they are not free-of-charge but I know from experience (since I've taken them and paid for them) they are top-class. There's even one on creating good models.

If you can't afford this, you could watch some vids on YT. There are whole courses on PBI and DAX. One such would be Avi Singh. Very good vids come from The Guy in A Cube or Curbal. I'm sure that if you search YT for "dimensional modeling" (which is what you should study for PBI), you'll find tons of material.

You can also have a look at this thread: Solved: Recommendations for Training Courses - Microsoft Power BI Community