Forum Discussion
how to work with FULLER OUTER Join table
Hi All,
I have four data sources.
2 are dimension tables for the SKU codes and for the Market hierarchy.
The other two are:
One source contains the order data (S1). The other contains (S2) constraints data.
I have connected S1 and S2 via full outer join and getting something like this:
| S1.Code | S1.Market | S1.Order | S1.Date | S2.Code | S2.Market | S2.Constraint | S2.Date |
| 121 | abc | 10000 | 7/20/2021 | 121 | abc | 2000 | 7/20/2021 |
| 130 | abc | 5000 | 7/28/2021 | ||||
| 150 | xyz | 1000 | 6/20/2021 |
Now the issue is that I m unable to connect it to dimension table because of some blank rows. If I connect it via S1.code then I miss some data in Source 2 (eg for code 130).
In excel I coul add a column which says if S1.code or S2.code is blank, we should take the not blank value. But how do I do this in PowerBI so that I can create a relationship and all data is captured?
Would highly appreciate any help on this!
Hi Anonymous
You can add a custom column and add below DAX formula.SCode = IF(AR[S1.Code] = BLANK() ,AR[S2.code],AR[S1.Code])
Now Just create relation ship with dimension table using column Scode = S1.code and Scode = S2.Code.
to know more about how to create a relationship follow this document.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships
Thanks,If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous
You can add a custom column and add below DAX formula.SCode = IF(AR[S1.Code] = BLANK() ,AR[S2.code],AR[S1.Code])
Now Just create relation ship with dimension table using column Scode = S1.code and Scode = S2.Code.
to know more about how to create a relationship follow this document.
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships
Thanks,If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.