Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
88 | |
75 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |