cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vgarlap
Post Patron
Post Patron

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

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 

Data Model.PNG

 

Capture2.PNGCapture.PNG

4 REPLIES 4
v-easonf-msft
Community Support
Community Support

Hi,  @vgarlap 

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:

113.png

 

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

Best Regards,
Community Support Team _ Eason

daxer-almighty
Solution Sage
Solution Sage

@vgarlap 

 

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.

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 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors