Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I want to create a table visual where data is coming from three different tables which are linked with each other on a key.
Table 1:
Sourceno | amount |
A1 | 12 |
A2 | 85 |
A3 | 61 |
A4 | 92 |
A5 | 11 |
B1 | 65 |
B2 | 25 |
B3 | 71 |
B4 | 31 |
B5 | 15 |
Table 2:
Sourceno | customername |
A1 | A |
A2 | B |
A3 | C |
A4 | D |
A5 | E |
Table 3
Sourceno | vendor |
B1 | V1 |
B2 | V2 |
B3 | V3 |
B4 | V4 |
B5 | V5 |
Expected output should be like mentioned below:
Amount | customer/vendor |
12 | A |
85 | B |
61 | C |
92 | D |
11 | E |
65 | V1 |
25 | V2 |
71 | V3 |
31 | V4 |
15 | V5 |
Please help me in achieving the desired output.
Hi,
Please try to create a calculated table as below:
Table =
UNION (
SELECTCOLUMNS (
'Table 2',
"customer/vendor", 'Table 2'[customername],
"Amount", RELATED ( 'Table 1'[amount] )
),
SELECTCOLUMNS (
'Table 3',
"customer/vendor", 'Table 3'[vendor],
"Amount", RELATED ( 'Table 1'[amount] )
)
)
The result shows:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto
As suggested by the other poster, you can append both Table2 and Table3 into one single "dimension" table and establish a 1:N relationship between the appended table and the "fact" table which has got the Amount
Then you can use RELATED() function with ISBLANK() to create a calculated column that will check if the source has a corresponding customer and if it doesn't it will pull up vendor. So we are looking at two RELATED() calls.
Keep in mind that you can do this operation without appending the tables and it would work just fine. But probably the real question would be how many rows are we looking at here in the "fact" table with "Amount" column. It might not be a good idea to create a calculated column there, especially with two RELATED() calls.
Hi @karun_r ,
Rows are in millions. As suggested by you, this will not be a good idea to create a calculated column there, especially with two RELATED() calls.
Is it possible to do the operation equivalent to RELATED during your query pull ? If your fact table has millions of rows, what is the source from which you are pulling it from ? If it's a SQL Server or any relational DB, it might be wise to do the operation there instead of thinking of ways to do in DAX. However, please let us know if you find any suitable solution for this without RELATED()
Hello @ask4atish ,
You may use Power Query for getting the desired output:
You may find sample pbix file here
Cheers!
Vivek
If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂
https://www.vivran.in/
Connect on LinkedIn
Hi @vivran22 ,
This i snot working in my case. Related function is not picking the tables to choose from. My tables are linked with 1:N and yours are 1:1.
Hi @ask4atish
You can append Table 2 and Table 3 in Power Query, but before you will need to rename both customer and vendor columns to customer-vendor
Or use Union Dax Function
Hi @Mariusz ,
I followed the steps provided by you. However, now amount field is showing blank or 0 whenever I perform sum on this.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.