How to create a dummy table ?

[Birinder]

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.

[Anonymous]

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.

[v-eqin-msft · Accepted 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.

[amitchandak]

@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

[Birinder]

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.

[amitchandak]

@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/

[Birinder]

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 ?

[amitchandak]

@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