Forum Discussion
Calculate Columns across different tables
Hello all
I am very new to DAX and Power BI so I apoligise for the probably simple question...
I have six different database tables that contain Nationality information. Each table represents a different Business Unit, however, all the tables contain the same information (the exact same software system for all). I am looking to collate two pieces of information:
1) The total number of nationalities
2) Total number 'on-roll'
I have managed to complete this for each Business Unit individually, however, I am now trying to get a Group level view, and am struggling...
Per Business Unit I have the fields txtNationality and txtID (filtered to 1 which is system status for current). I simply Count Distinct ID numbers against each Nationality which seems to work fine. I now need to do this for all 6 Business Units together, though it could be possible that across the 6 different systems someone may have the same txtID number.
Can anyone please advise how this may be completed? I have read about using the RELATED function, ADD COLUMNS, SUM etc. but cannot seem to get this to work. I'm not sure I am managing the relationships between the tables correctly.
Thanks very much in advance for any help.
2 Replies
I would probably look into creating a single table from the 6, that will make things easier. Here is an article that shows an example:
https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-shape-and-combine-data/
When you import data into Power BI Destkop, it will detect the relationships between tables, you can also click "Manage Relationships" to edit the relationships.
With relationships, you can join your data in DAX using RELATED() function. Without relationships, you can still join tables using LOOKUPVALUE(). For more information, please see: From SQL to DAX: Joining Tables
Also you can join tables using "Merge Queries" in Query Editor as kdejonge mentioned.
Regards,
