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,
Skilled Sharer
Could you please post your relationships diagram picture and your M code from Power Query .
N -
Hi N,
Thanks for your reply. Please see below some pseudo code based on the code provided by SQL Profiler. I have just removed the actual names and replaced with TABLE_A, TABLE_B and TABLE_DATE.
Please see below an image of our relationships. There are also some additional tables with Many:1 relationships with TABLE_A.
SELECT TOP (1000001) [t1].[Call_ID],[t6].[Task_ID], COUNT_BIG(*) AS [a0] FROM ( ((select [$Table].[Task_ID] as [Task_ID], [$Table].[CRM_Call_ID] as [CRM_Call_ID] from TABLE_B as [$Table]) AS [t6] left outer join (select [$Table].[Call_ID] as [Call_ID], from TABLE_A as [$Table]) AS [t1] on ( [t6].[CRM_Call_ID] = [t1].[Call_ID] ) ) left outer join (select [_].[DATE_ID], [_].[DATE], [_].[NEXT_DAY_DATE], [_].[YEAR], [_].[YEAR_QUARTER], [_].[YEAR_MONTH], [_].[YEAR_DAY_OF_YEAR], [_].[QUARTER], [_].[MONTH], [_].[DAY_OF_YEAR], [_].[DAY_OF_MONTH], [_].[DAY_OF_WEEK], [_].[YEAR_NAME], [_].[YEAR_QUARTER_NAME], [_].[YEAR_MONTH_NAME], [_].[MONTH_YEAR_NAME], [_].[YEAR_MONTH_NAME_LONG], [_].[QUARTER_NAME], [_].[MONTH_NAME], [_].[MONTH_NAME_LONG], [_].[WEEKDAY_NAME], [_].[WEEKDAY_NAME_LONG], [_].[START_OF_YEAR_DATE], [_].[END_OF_YEAR_DATE], [_].[START_OF_QUARTER_DATE], [_].[END_OF_QUARTER_DATE], [_].[START_OF_MONTH_DATE], [_].[END_OF_MONTH_DATE], [_].[DATE_FORMAT_YYYY_MM_DD], [_].[DATE_FORMAT_YYYY_M_D], [_].[FISCAL_YEAR_NAME], [_].[FISCAL_YEAR_START], [_].[FISCAL_YEAR_END], [_].[FISCAL_QUARTER_NAME], [_].[FISCAL_MONTH], [_].[LAST_FRIDAY_OF_MONTH], [_].[NEAREST_FRIDAY_TO_END_OF_MONTH], [_].[WEEKDAY_FLAG], [_].[WEEKEND_FLAG], [_].[BANK_HOLIDAY_FLAG] from TABLE_DATE as [_] where convert(date, [_].[DATE]) >= convert(datetime2, '2017-01-01 00:00:00') and convert(date, [_].[DATE]) < convert(datetime2, '2018-01-01 00:00:00')) AS [t4] on ( [t1].[ContactDate] = [t4].[DATE] ) ) WHERE ( ( [t1].[ContactDate] = CAST( N'2017-08-06 00:00:00' AS datetime) ) AND ( [t4].[MONTH_YEAR_NAME] = 'Aug 2017' ) ) GROUP BY [t1].[Call_ID],[t6].[Task_ID]Thanks in advance for any help.
Carl
- nickchobotar
It appears it's more of a dimensional modeling issue in general than just a relationship issue. We will need to see the diagram of the whole model. Please identify your dimensions and facts. If possible use named tables.
Also, which fields are you using to build the visualisation you mentioned in the first post? Please post the M code of the tables in questions in case if you used Power Query for merging, appending anything to do with querying two or more tables.
CRMCalls is a fact table and the 1:* relationship it has with TaskingTask it suspicious. Do you have dup rows in TaskingTask?
Tks, N -
Hi Nickchobotar,
Thanks again for your reply.
We are not using a dimensional model as the source for our Power BI model. We aren't quite there yet. At this stage we are trying to provide an analytical function within the business with access to a number of datasets so that they can self serve agaist our reporting database.
We are using Views to provide different related information such as Customer Accounts details, Property details, Customer Calls (CRM Calls) and Tasks. The CRM table has a One to Many relationship with the Tasks table as each call from a customer could generate multiple tasks each being directed to a different employee within the business. There are no transformations taking place within Direct Query as this has been managed within the views and therefore the M query will only show the connection to these Views
Even when removing all other tables from the Power BI file and just keeping the relationships between the CRMCalls table, the TaskingTasks table and the CallDateDimDimensions table and attempting to visualise, as a table, the columns from the CRMCalls table and the Task_ID from the TaskingTasks table with a slicing filter being applied on the CallDateDimDimensions Month-Year column the code from SQL Profiler shows that the first table referenced within the FROM clause is theTaskingTasks table.
Within the 'Manage Relationships' button I can see that the reason for this is that the relationship between the TaskingTasks table and the CRMCalls table is defining the TaskingTask within the FROM side of the relationship.
However, when I attmept to change this and JOIN from the CRMCalls table to the TaskingTasks table it will not save and the relationship is reversed automatically.
So, my question is why can't I JOIN FROM the CRMCalls table to the TaskingTasks table? Surely, it should just let me use a LEFT JOIN to return all rows from the CRMCalls table and only those rows from the TaskingTasks which match. I realise that this would cause duplicate rows for the CRMCalls table but this shouldn't be the issue should it?
Thanks