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

2 data sources joined with a Key whose value may be different at some instances

Hi all: Good Day! I have 2 different data sources (e.g. table1 & table2) coming from 2 different databases. I am joining these 2 tables on a key "Pipeline" [text]  to pull ‘events’  from table1 and ‘Scheduler’ from table2 for a “Pipeline” [ using a “Table” visual to show the data]. The relation diagram is shown below. Table1 is the left most table and table2 is the right most table.

Since there are instances where the “Pipeline” names don’t exactly match, I am missing some records.

There is an excel file, table3 (the middle table ) to map the names: basically two columns, and an example of how the name varies and now is mapped in table3

ANR                     ANR Pipeline

How can I use this  table 3 in the relation so that I don’t miss data when table1 has ANR for a “Pipeline” and table2 has “ANR Pipeline” ?

 

sohananahid_0-1594336330263.png

Thanks in advance! 🙂

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: 2 data sources joined with a Key whose value may be different at some instances

I personally would not use the text "NULL" in the fields and that can be confusing vs a legitimate NULL, which is really a BLANK() in DAX. Use something else, like "None." If you are using a real null (blank()) then you can still create the relationship but you'll have values show up on one side and not the other, and can make some of the visuals have blanks where you have things like item numbers or whatever.

 

As to case sensitivity, it depends. DAX is not case sensitive. "None" = "NONE" = "noNE"

Power Query though is case sensitive. You would want to wrap such columns with Text.Lower(), Text.Upper, or Text.Proper() to have the same case on each side of a merge.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Highlighted
Super User VI
Super User VI

Re: 2 data sources joined with a Key whose value may be different at some instances

If that is what you see in DAX, that is the text Null and null, not a real null. A real null is blank.

If you type that in Power Query, it shows up green, showing it is a keyword:

edhans_0-1594678580714.png

Then shows up in italics in the Power Query window

edhans_1-1594678605042.png

Then shows up blank in the DAX model, and would equate to the BLANK() function, and can be trapped with IsBlank(Table[Field]), or Table[Field] = BLANK() would evaluate to TRUE().

edhans_2-1594678657707.png

So that Null and null are text if that is DAX. If it is Power Query it is also text, as a legit null shows up as null there.

 

All null values will join a the same thing in Power Query, and all BLANK() in DAX will form a filter relationship with other BLANK() values.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

12 REPLIES 12
Highlighted
Super User VI
Super User VI

Re: 2 data sources joined with a Key whose value may be different at some instances

Get rid of the many-to-many bi-directional relationship you have between the two tables and only use the bridge table in the middle. You will then be able to activate that relationship.

 

Then use the bridge table filelds in visuals. 

Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Super User IX
Super User IX

Re: 2 data sources joined with a Key whose value may be different at some instances

@sohananahid , You do not need that direct join M-M joins. Use the table with 1-M join middle table.

 

When you take data from all three tables you can unsummarized data only from 2 tables. One of them will the middle table.  For Measures and aggregated columns no problem.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: 2 data sources joined with a Key whose value may be different at some instances

Hi @sohananahid ,

 

For the relationship, recommend you create the star schema instead of circle schema, so you may delete the secondary relationships among the left table 'natgas ...' and right table 'user …' . 

 

Then create active relationship between the middle table and the right table 'user …',  and change the Cross filter direction of relationships among the three tables above from Single to Both , which will take these tables treated as a single table.

 

In this way,  when you make some changes or interact in someone table, the other table objects will return corresponding matched result.  See more:Create and manage relationships in Power BI Desktop .

Star schema.png

 

 

 

 

 

 

 

 

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Post Patron
Post Patron

Re: 2 data sources joined with a Key whose value may be different at some instances

Hi @amitchandak: good day! Thanks for the response! One things I forgot to mention is that the middle table so far only has the pipeline names that don't match between table1 & table2. When I remove the many-many relationship between the 2 tables [shown below], in my 'Table' visual:  "Pipeline", "event" & oher info from table1 show up good as before. But as I try to show "scheduler" from table2, the visual doesn't show anything and gives error. Earlier [w/o mapping table] it showed some records, but missing records with difference in  "Pipeline" names between table 1 & 2 [for the same pipeline] as I mentioned earlier.

sohananahid_0-1594390756665.png

 

Do I have to have all "Pipeline" names [those who match exactly too] and also "Scheduler" name in the Mapping table to use it as the new table2?

Thanks in advance!

Highlighted
Super User VI
Super User VI

Re: 2 data sources joined with a Key whose value may be different at some instances

Yes @sohananahid - that bridge table should have ALL of the data from both sides to create the bridge, not just the missing data. Even if the Many to Many Bi-Directional relationship was advised (and it is not) you'd have some really horrible DAX code to write to handle things that were in relationship 1 via M2M and then the missing items in relationship 2 via the bridge. 

 

Microsoft recommends minimizing use of both Many-to-Many relationships and Bi-Directional filtering. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships
Microsoft Guidance on Importance of Star Schema



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Post Patron
Post Patron

Re: 2 data sources joined with a Key whose value may be different at some instances

Hi @v-xicaiThanks for the response! I followed the star schema instructions, but still having the issue that I mentioned with error showing up the visual with data from table2 ['Scheduler']. It's happening as @edhans just confirmed that my middle "bridging" table has to have ALL the data for the "Pipeline" names- those that match perfectly besides those don't!

Best, ~Sohana

Highlighted
Post Patron
Post Patron

Re: 2 data sources joined with a Key whose value may be different at some instances

Hi @edhans : Thanks for your response! 🙂 I am now thinking if I have to create this "bridging" table with ALL "Pipeline" names between the 2 sources [table 1 & 2] then probably I also add another column- 'Scheduler' since that the main info I need from table 2! Then I won't need table 2! It will be a lot of manual work to enter all those info, but may be worth it! Best, Sohana

Highlighted
Super User VI
Super User VI

Re: 2 data sources joined with a Key whose value may be different at some instances

Sounds good @sohananahid - you are starting to see how important a good model is. I think a good model is half the battle. So much of the garbage DAX I've written over the years would have been much better and easier if I had a better understanding of modeling back then. 😁

 

Please mark one of these posts as the solution so others can find it and know it is resolved. 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Post Patron
Post Patron

Re: 2 data sources joined with a Key whose value may be different at some instances

Hi @edhans : Good day! 🙂 As I am buildin the bridge table [the middle one], I find there are some 'Pipeline' in table1 don't match with that in table2 and also vice versa. 1st column is associated with the database table that has different events info and the 2nd column is for scheduler info.

1. Can I use 'NULL'  for those values where I don't have any matching value rather than keeping the cell empty? power BI is case-insenstive whether I use "NULL' or 'null', right?

2. Will it create error when trying to use this bridge table with null values

3. will i still miss events/ scheduler info for the NULL values?

 

Much appreciated in advance! Best, Sohana

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors