Forum Discussion
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.
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:
Then shows up in italics in the Power Query window
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().
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.
Super UserAnonymous , 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.
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.
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!
- edhans
Community Champion
Yes Anonymous - 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 SchemaHi 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
- edhans
Sounds good Anonymous - 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.