Forum Discussion
DAX Table Joins
Hi All,
I am trying to join three tables using a Dax query. These tables have relationship with one another but in power BI reports I am loosing data for few payroll periods due to direct relationship between Payroll (P) and Termination (T) Table on P_ID. to avoid this data loss I have to add one more condition containing termination date from A less than EndDate of table P and T.Status =1 or 2.
I have written SQL query for this which is working fine in my SSRS reports. Now I want to transfer this report to power BI and I am all confuse how to do this.
SELECT *
FROM A
INNER JOIN T ON A.Id = T.A_Id
INNER JOIN P ON (((A.TerminationDate <= P.EndDate)
AND (T.Status = 1 or T.Status = 2))
OR (T.P_Id = P.Id))
I am not sure how to join TerminationTransaction to PayrollSchedule based on two more conditions than only P_ID.
Does anyone know how can I do this in power BI.
So, if importing from SQL to Power BI Desktop, you can use the SQL Server connector and on the pop-up screen you will see down at the bottom, "Advanced options". Expand that and you can paste in your SQL statement.
So, I'm not sure that this is really a DAX-type question. I think you need to start thinking in terms of visuals rather than just data manipulation (SQL queries). For example, if we look at your query:
SELECT * FROM A INNER JOIN T ON A.Id = T.A_Id INNER JOIN P ON (((A.TerminationDate <= P.EndDate) AND (T.Status = 1 or T.Status = 2)) OR (T.P_Id = P.Id))
Now, I do not have your data or specific information what you are trying to accomplish but ,to me, this means that you have 3 tables, A, T and P. They are related to one another via an "Id" field in each. So, in Power BI you would import the tables independently and then create a relationship between the tables on the "Id" fields. You could then create a column or measure to check if A[TerminationDate] is less than or equal to P[EndDate]. The you could create a table visualization with all of the columns from A in it and then filter the visualization to your column/measure that you created and a T[Status] of either 1 or 2.
7 Replies
- Greg_Deckler
Community Champion
Well, I would think that you would relate the tables and then do a CROSSJOIN function in DAX but you may have more luck with this in Power Query as this allows fairly complex, conditional joins. Or, just use your SQL query as the SQL query for your Power Query query using the "Advanced" functionality when creating your query in Power Query and this will bring back a table with all of the joins performed.
Wow, I was able to use the word query six times in a single sentence!!
hello Greg_Deckler
Thank you for your reply.
Can you please guide me how to load data in power BI advance editor using SQL query?
Also my current power BI datamodel is using these tables with many other tables for diffrent reports, can I load data with SQL query in same data model, will it affect my other reports or I need to prepare new data model for this report?
Thjank you.
- Greg_Deckler
So, if importing from SQL to Power BI Desktop, you can use the SQL Server connector and on the pop-up screen you will see down at the bottom, "Advanced options". Expand that and you can paste in your SQL statement.
