left join
2 Topics
Left outer join by combing two tables
Hi All, I am very new to power bi. I am trying to create a new output table using the calculated columns from the other table. I tried using the natural join function but I was un-able to create a new table sucessfully. I know that this can be achieved easily in the sql but I am not quite familiar with the DAX. Any suggestions will be very helpful. Thanks Table 1 Month Year Cat 202401 c1 202402 c1 202403 c1 202404 c1 202405 c1 202401 c2 202402 c2 202403 c2 Table 2: Month Year Cat Code Date 202401 c1 1 23-Jan-24 202402 c1 1 2-Feb-24 202403 c2 2 31-Mar-24 202405 c1 1 24-May-24 202405 c1 2 25-May-24 Output: Month Year Cat Code Date 202401 c1 1 23-Jan-24 202402 c1 1 2-Feb-24 202403 c1 202404 c1 202405 c1 1 24-May-24 202405 c1 2 25-May-24 202401 c2 202402 c2 202403 c2 2 31-Mar-24 202404 c2 202405 c2Solved1.4KViews0likes7Comments
DAX measure with left outer join, multiple to multiple
I have one table in data model with one row per employee, their current end previous manager, and their current and previous output: EmployeeID Current Manager Current Output Previous Manager Previous Output 1 A 10 B 5 2 A 20 A 15 3 B 30 A 40 4 B 5 B 10 In the reports view, I show the sum of current and previous output by current and previous manager respectively: Current Manager Current Output A 30 B 35 And: Previous Manager Previous Output A 55 B 15 I would like to left join the 'current output' table with the 'previous output' table on Current Manager = Previous Manager so I can calculate the output delta. What I want is this: Current Manager Current Output Previous Output Delta Output A 30 55 -25 B 35 15 20 I have tried writing a measure to give the answer, but can't figure out how to pass the values of the delta: Create Delta Table = VAR x2=GENERATEALL( SUMMARIZE('EmployeeTable', EmployeeTable'[Current Manager], "Sum Current Output", CALCULATE (SUM('EmployeeTable'[Current Output]))) ,SUMMARIZE('EmployeeTable', 'EmployeeTable'[het_name], "Sum Previous Output", CALCULATE (SUM('EmployeeTable'[Previous Output]))) ) RETURN x2Solved855Views0likes2Comments