Forum Discussion
Create a calculated (DAX) table using related tables?
I know this is possible to do with Power Query (or just dropping the fields in a visual, since the tables are related) but I would like to know if this is possible to do with just DAX.
I have 2 tables, EmployeeData and TrainingData which are related 1:M by the EmpID field:
| EmpID | EmpName | Country | ||
| E001 | John | USA | ||
| E002 | Jane | USA | ||
| E003 | June | USA |
| EmpID | Training | TrainingDate | ||
| E001 | Scheme A | 4/2/2023 | ||
| E001 | Scheme B | 1/3/2023 | ||
| E001 | Scheme C | 12/5/2022 | ||
| E002 | Scheme B | 10/15/2022 | ||
| E003 | Scheme A | 4/2/2023 |
Is it possible in DAX to create a calculated table that shows only the data relevant to Scheme A? The expected output would be this:
| EmpID | EmpName | Country | Training | TrainingDate | ||||
| E001 | John | USA | Scheme A | 4/2/2023 | ||||
| E003 | June | USA | Scheme A | 4/2/2023 |
I have been looking into this for some time and I am not sure if the search query I am using is correct. Most of the calculated table tutorials I've seen contain aggregations, and in our case, there really isn't one. We need to be able to make it in DAX because the DAX formula will be used in PBI Report Builder - is what we're trying to do possible to create with just DAX?
2 Replies
olimilo , In power query you can use merge
Merge Tables (Power Query) : https://youtu.be/zNrmbagO0Oo
In this case DAX summarize should also work
summarize(Table2, Table2[EMP ID], Table1[Name], Table1[Country], Table2[Training], Table2[Date])
Also check Natural Join - https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
Because they are joined, you can put all columns as unsummarized in visual
Is there a reason why Table 2 is used as the table for the SUMMARIZE function and not Table 1?
