Relationships

[Carl_Thompson]

Hi,

We have created a model and defined the relationships between tables.  When creating a visualisation in the report pane and capturing the SQL statement generated by Power BI within SQL Profiler when we apply a filter, we have found that the order of the tables is in an order that does meet our requirments and is limiting our dataset (outside of the filter).  The first table in the FROM clause has less rows than our 'main' table and therefore this is limiting our resultset.

We have TABLE_A and TABLE B with a 1:Many relation as TABLE_A:TABLE_B (single cross-filtering direction).  When capturing the generated SQL statement TABLE_B is the first table in the FROM clause with a LEFT JOIN to TABLE_A.  However, TABLE_A is our primary table and we would want this table to be our first tablein the FROM clause with LEFT JOINs coming from this table.

Is there a way to define the primacy / order of tables?  Does the behaviour we are observing indicate that we have made a mistake with our table relationships?

Many thnaks in advane for nay assistance provided.

Carl

[nickchobotar]

@Carl_Thompson

Could you please post your relationships diagram picture and your M code from Power Query .

N -

[Carl_Thompson]

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]

@Carl_Thompson

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 -

[Carl_Thompson]

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

[nickchobotar]

@Carl_Thompson

You cannot change the cardinality between CRMCalls table and TaskingTasks due data structure of these tables and its  1:* relationship to each other.  

CRMCalls duplicates is not an issue you will also get CRMCalls blanks for TransID that have no CRM ID you can handle that with  DAX.

Good thinking on building off of the sql views, it will save you a lot of headache in the future. 

[Carl_Thompson]

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,

[Carl_Thompson · Accepted solution]

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,