DAX Table Joins

[apatil]

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. 

[Greg_Deckler · Accepted solution]

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.

[Greg_Deckler]

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!!

[apatil]

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 · Accepted solution]

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.

[apatil]

Hello @Greg_Deckler

I am able to import the intended data using SQL query. 🙂 

I will work on my reports now. Thank you so for your suggestion, my report was pending due to this data load.

Though this solution worked for me, do you suggest this is the best practice or I could also do this with DAX query? 

I am learning DAX query and was trying to use FILTER with RELATED function but was not able to run this query due to many errors. 

For my understanding can you please suggest what would be the DAX way to get this data?

Appreciate your help so much. 

[Greg_Deckler · Accepted solution]

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.

[apatil]

Hello @Greg_Deckler

That too worked for me! 😊

I should have break the steps as you have mentioned. I was trying to write s single query in DAX same as I wrote in SQL with FILTER/ RELATED /AND/ OR which was very complex and confusing to execute. 

My visualizations are simple tabular report where I am getting detailed data by filtering employee payroll category by year and payroll schedule. I imported a table view using advance query in my existing data model only for this report and connected this view with table A on "Id" filed for further relational visualizations. 

Appreciate your help.

Thank You. 

[Greg_Deckler]

Happy to help. Power BI definitely forces you to have to switch gears in terms of how you approach an issue sometimes!!