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.
Community ChampionWell, 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.
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
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.