USERELATIONSHIP Bug when using Live Connection (SQL 2014 compat. 1103)

[JesperP]

While doing a very simple operation that has worked for me in the past countless times, I ran into a bug when using Power BI and a live connection to a Tabular model.

It seems that when multiple relationships have been created on a datefield, and you have used the USERELATIONSHIP function, it will not always work properly in Power BI - Live Connect. The bug only happens in this combination:

Power BI - Other sources = Expected Behavior

Power BI - Live Connection SASS Tabular = Bug

Excel - Other sources = Expected behavior

Excel - Connection SASS Tabular = Expected behavior.

As a result, I cannot include an example file (as it will work properly in that case and the bug will not show). I did fake the situation in Excel, show you can show what I mean:

Example (Datamodel)Example (Report)

[v-qiuyu-msft]

Hi @JesperP,

Based on my test, the issue is related to SSAS tabular model design. Please open your SSAS tabular project in Visual Studio, check if these two relationship Filter Directions are set as To Both Tables. If it is, please don't select this option, select To Ticket instead. Then process and deploy project again. Test on Power BI desktop you should see the desired results. 

Best Regards,
Qiuyun Yu 

[JesperP]

Hi @v-qiuyu-msft,

Thank you very much for your quick response & help! Interesting to see it seems to work fine for you.

The relationships are already set as <<To Ticket. Because we use SQl 2014, we do not have any other option (filter direction both was introduced in later versions I believe).

I have included a screenshot of our actual datamodel. The Power BI version is exactly similar as yours. What is the version of the SQL server you have used?

Screenshot SASS Datamodel

[JesperP]

We are currently quite stuck when this bug remains unsolved.

[v-qiuyu-msft]

Hi @JesperP,

I tested with SSAS tabular 13.0.4522.0 originally. From the screenshot, it's relation between CreatedAt and PK_date, please check SolvedAt and PK_date, ensure it's Filter Direction is To Ticket as well. 

Today I installed SSAS tabular instance 2014 version, and deployed the tabular project compatibility level 1103 to this server, then tested Power BI desktop, it's working fine as well. 

Please download our tabular project and test on your side. You can create Time and Ticket tables in SSMS via: 

Create table Ticket (ID int, CreatedAt date, SolvedAt date)
insert into Ticket values 
(1,'2018-2-1','2018-2-1'), 
(2,'2018-2-1','2018-2-2'), 
(3,'2018-2-1','2018-2-2'), 
(4,'2018-2-1','2018-2-3'), 
(5,'2018-2-2','2018-2-4'), 
(6,'2018-2-2','2018-2-3'), 
(7,'2018-2-2','2018-2-2'), 
(8,'2018-2-3','2018-2-3'), 
(9,'2018-2-3','2018-2-4'), 
(10,'2018-2-4',NULL) 
select *from Ticket 
Create Table Time 
(PK_Date date, Year int, Month varchar(50)) 
insert into Time values 
('2018-2-1',2018,'Feb'), 
('2018-2-2',2018,'Mar'), 
('2018-2-3',2018,'Apr'), 
('2018-2-4',2018,'May'), 
('2018-2-5',2018,'Jun'), 
('2018-2-6',2018,'Jul'), 
('2018-2-7',2018,'Aug'), 
('2018-2-8',2018,'Sep'), 
('2018-2-9',2018,'Oct'), 
('2018-2-10',2018,'Nov') 
select *from Time

Best Regards,
Qiuyun Yu 

[JesperP]

Hi @v-qiuyu-msft,

Thank you very much for helping us further. I've used your model, and I can confirm that I am experiencing the same issue in this model as well. So at first it seems to work, but when you actually filter, it will not display the correct result. I've included screenshots below. Notice how the first table indicates that 3 tickets have been solved on the second, but when you filter on that date, the second table will not show the 3 dates, but just the one where the created date was on the same date. This is inconsistent with how power bi and power pivot work in all other tests I did.

No filterfiltered 02/02/2018

[JesperP]

I also made a screenshot of how Power BI usually treats these kind of relationships. This screenshot is of a project file that gets its data of Excel. Exactly the same model, different outcome when filtering.Excel - Power BI = Correct outcome

[JesperP]

Still haven't found a solution to this. Any ideas?

[Thomas_VW]

@v-qiuyu-msft Do you have any idea why the outcomes are different? I cant figure out why it could be different...

[JesperP]

Is there a formal way to report this bug? We can't get any further now, so the whole project is on hold...

[v-qiuyu-msft]

Hi @JesperP,

Sorry for the late response. I have reported this issue internally: CRI 83934292. Will update here once I get any information. 

Best Regards,
Qiuyun Yu