Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi all!
This is the situation:
Example of how the table in the ERP-system looks like: in yellow the column which is added with the SQL-statement.
I hope someone can help me out! Thanks in advance,
Solved! Go to Solution.
Hi @DanielB_NL
One workaround is that creating a bridge table,
1. extract the purchase_receipt_nr and article_code from both the tables
2. append these 2 new tables add a new column to concatenate 2 columns, and remove duplicates --> now this is a dimension table with 3 columns (purchase_receipt_nr, article_code & concatenate of these 2)
3. do the concatenate in both the detailed tables
4. make the relationship from the dimension table to both the detailed tables with concatenate column
5. take the purchase_receipt_nr and article_code from the dimension and others as regular measures...
hope this will help..
Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18
Hi @mohammedadnant,
It was a step in the right direction. In the end it ended up in a many-to-many relation. I used your suggestion to create a dimension table, but added the sequence no. column to this table, created the concatenated column based on purchase_receipt_nr and article_code and removed duplicates based on the concatenated column. In practice, the chance that on one Purchase Receipt No. there are more of the same Article is very small, so the 'damage' done by removing the duplicates is close to zero.
Thanks Mohammed,
Can you please elaborate how to do this and how to extract the purchase_receipt_nr and article_code from both the tables?
For your information: I'm connecting to the tables in DirectQuery mode: I don't know if this relevant for your solution? Kind regards,
Hi @DanielB_NL
One workaround is that creating a bridge table,
1. extract the purchase_receipt_nr and article_code from both the tables
2. append these 2 new tables add a new column to concatenate 2 columns, and remove duplicates --> now this is a dimension table with 3 columns (purchase_receipt_nr, article_code & concatenate of these 2)
3. do the concatenate in both the detailed tables
4. make the relationship from the dimension table to both the detailed tables with concatenate column
5. take the purchase_receipt_nr and article_code from the dimension and others as regular measures...
hope this will help..
Thanks & Regards,
Mohammed Adnan
Learn Power BI: https://www.youtube.com/c/taik18
Hi @mohammedadnant,
It was a step in the right direction. In the end it ended up in a many-to-many relation. I used your suggestion to create a dimension table, but added the sequence no. column to this table, created the concatenated column based on purchase_receipt_nr and article_code and removed duplicates based on the concatenated column. In practice, the chance that on one Purchase Receipt No. there are more of the same Article is very small, so the 'damage' done by removing the duplicates is close to zero.
Hi @DanielB_NL
Thanks for your reply, in your case, you are right, sometimes it is needed,
could you please give thumbs up to my answer.
Thanks & Regards,
Mohammed Adnan
User | Count |
---|---|
85 | |
79 | |
64 | |
52 | |
46 |
User | Count |
---|---|
101 | |
49 | |
42 | |
39 | |
38 |