Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there,
My query is very simple.
Suppose, We have 3 different data tables, which are related to each other.
Now I want to show each column from a different data source in a new dummy table.
Like, 3 column table, in which, each column is extracted from a different data table.
TABLE 1
ID | Product |
1 | Household |
2 | Outdoor |
TABLE 2
Date | Product |
1-1-2021 | Household |
2-12-2021 | Outdoor |
TABLE 3
Date | Price |
1-1-2021 | 1000 |
2-12-2021 | 2000 |
MY DESIRED TABLE
Date | Product | Price |
1-1-2021 | Household | 1000 |
2-12-2021 | Outdoor | 2000 |
Please note that I want to use this as a code, when creating a new table in data model.
Thanks in Advance.
Solved! Go to Solution.
Hi @Birinder ,
Based on my test, we could not use DAX to create calculated column and calculated table when using Live connetion mode:
If the original relationship is as below:
You could directly drag fields to Table visual:
Or please create a measure instead:
Measure = CALCULATE(SUM('TABLE 3'[Price]),FILTER('TABLE 3',[Date]=MAX('TABLE 2'[Date])))
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Birinder , this is my method to achieve the result table:
ADDCOLUMNS(Table01, "Date", LOOKUPVALUE(Table02[Date], Table02[Products], Table01[Product])) |
This will achieve a table as below:
After that use "New Column" to add in te Price.
CALCULATE(SUM(Table03[Price]), FILTER(Table03, Table03[Date] = 'Union'[Date])) |
Thank you.
Hi @Birinder ,
Based on my test, we could not use DAX to create calculated column and calculated table when using Live connetion mode:
If the original relationship is as below:
You could directly drag fields to Table visual:
Or please create a measure instead:
Measure = CALCULATE(SUM('TABLE 3'[Price]),FILTER('TABLE 3',[Date]=MAX('TABLE 2'[Date])))
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Birinder , Merge in power query can help for that.
Merge 1 and 2 based on date and again merge table 3 based on date
Merge Tables (Power Query) : https://www.youtube.com/watch?v=zNrmbagO0Oo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=16
Hi @amitchandak
I know about Power query.
But I want to do it in DAX.
Like when we write a code to add a table in model frame.
What actually I am supposed to write in there.
Sorry for such a twisted question.
@Birinder , One way is to join tables 1 and 2 on product and then join and 2 and 3 date and put the required columns in visual
Or you can use natural left join https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
I guess you still dont get it. No worries. I will explain as best as I can.
The data is live connected.It is live, so nothing appears in power query. I know I can join on table and product. But for that I need Power query. I don't want to use power query.
What to write here. Like we do function, "SELECTCOLUMNS" and input parameters to get a new desired table.
I want something like this.
Is this possible ?
@Birinder , if you are able to create DAX table then NATURALLEFTOUTERJOIN and NATURALINNERJOIN are DAX functions. for which I shared link https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
Sorry for confusion
User | Count |
---|---|
98 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
58 |