Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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.
Thank you so much. Turns out was pretty simple 😊
Really appreciate your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |