Forum Discussion
Relationships
Hi,
With some help, we've bee able to determine why we were not seeing the behaviour we wanted.
As a Power Model is created as a Tabular Analysis Service in the back-end this model attempts to enforce INNER JOIN relationships between tables. In our example we wanted to exhibit LEFT JOIN behaviour from our CRM table to our Task table. A CRM record could have either no associated tasks, one associated task or multiple asociated tasks and we wanted to return all CRM records and the information of related tasks, if present. However, when viewing the query passed back to the server, via SQL profiler, we could see that the first table referenced was the Task table with a LEFT OUTER join to the CRM table, which in essence, enforced an INNER JOIN relationship.
In order to exhibit the LEFT JOIN behaviour we wanted there is a field option within the Report Pane of Power BI Desktop once you have dragged a field into the 'Values' section of the visualisation. If you select the little dropdown arrow to the right of the field which has come from the table you want to left join TO (in our case the Task table), there is an otion of 'Show Items with No Data'.
With this option selected we then profiled the query passed back to the server and the query showed the first table to be the CRM table with a LEFT JOIN to the Task table, as required.
So, the solution to our problem was unbelievably simple. However, if you are unaware of this option or that this needs to be ticked in order to enforce LEFT JOIN behaviour then this could cause large issues in results.
Hopefully, this will help anyne who encounters a similar problem as we weren't able to find much help on the internet. Maybe because it's so simple...
Thanks,
Hi Nickchobotar,
Thanks for your reply again and I hope you had a nice weekend.
However, I still don't understand why I am unable to have the CRM table in the FROM and then LEFT JOIN to the Tasking table?
It is possible for the Tasking table to have a Task row with no corresponding CRM table row, however, even when I exclude Tasks rows with no CRM ID from the Tasking View to ensure that all task rows will match to the CRM table I am still unable to set the CRM table as the first table the SELECT is applied to.
I want to be able to see all rows from the CRM table and only information from the Tasking table when there is a row match. At the moment I am only seeing rows from the CRM table where there is a matching row in the Tasking Table because the SELECT is first beng applied to the Tasking table.
I can write my desired behaviour in SQL with no problems so why can't I enforce that behaviour in the Power BI model.
I imagine there must be something I am not considering or not doing correctly in the Power BI Desktop, however, at the moment I don't understand why It will not allow me to LEFT JOIN FROM the CRM table TO the Tasking table?
Thanks,
Hi,
With some help, we've bee able to determine why we were not seeing the behaviour we wanted.
As a Power Model is created as a Tabular Analysis Service in the back-end this model attempts to enforce INNER JOIN relationships between tables. In our example we wanted to exhibit LEFT JOIN behaviour from our CRM table to our Task table. A CRM record could have either no associated tasks, one associated task or multiple asociated tasks and we wanted to return all CRM records and the information of related tasks, if present. However, when viewing the query passed back to the server, via SQL profiler, we could see that the first table referenced was the Task table with a LEFT OUTER join to the CRM table, which in essence, enforced an INNER JOIN relationship.
In order to exhibit the LEFT JOIN behaviour we wanted there is a field option within the Report Pane of Power BI Desktop once you have dragged a field into the 'Values' section of the visualisation. If you select the little dropdown arrow to the right of the field which has come from the table you want to left join TO (in our case the Task table), there is an otion of 'Show Items with No Data'.
With this option selected we then profiled the query passed back to the server and the query showed the first table to be the CRM table with a LEFT JOIN to the Task table, as required.
So, the solution to our problem was unbelievably simple. However, if you are unaware of this option or that this needs to be ticked in order to enforce LEFT JOIN behaviour then this could cause large issues in results.
Hopefully, this will help anyne who encounters a similar problem as we weren't able to find much help on the internet. Maybe because it's so simple...
Thanks,