cancel
Showing results for 
Search instead for 
Did you mean: 

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

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:

 

001.pngExample (Datamodel)002.pngExample (Report)

Status: New
Comments
Moderator

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. 

 

w1.PNG

 

w2.PNG

 

 

Best Regards,
Qiuyun Yu 

Frequent Visitor

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?

 

 

Capture.PNGScreenshot SASS Datamodel

Frequent Visitor

We are currently quite stuck when this bug remains unsolved.

Moderator

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. 

 

q5.PNG

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 

Frequent Visitor

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.

 

003.pngNo filter004.pngfiltered 02/02/2018

Frequent Visitor

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.005.PNGExcel - Power BI = Correct outcome

Frequent Visitor

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

Visitor

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

Frequent Visitor

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

Moderator

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