Forum Discussion
How to create a dummy table ?
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.
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.
7 Replies
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/
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.
