Forum Discussion
DAX Table Joins
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.
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.
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.
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.
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.
Happy to help. Power BI definitely forces you to have to switch gears in terms of how you approach an issue sometimes!!