cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lasselakan Regular Visitor
Regular Visitor

SSAS Tabular relationships problem (RIGHT OUTER JOIN equivalent)

EDIT: I tried a solution using a bridge-table. So instead of this:

SalesLines 1-----<>-----* Reasons

I can now do this:

SalesLines 1-----<>-----* Bridge *-----<-----1 Reasons

 

 

The advantage of doing the second way is that I don't need to create any "no reason"-records in Reasons. I think this solution will do the job, but don't hesitate to suggest anything you'd think be better!

 

--------------------------

I have a question regarding table relationships in SSAS Tabular. I have a fact-table with SalesLines and another "kind of dimension"-table with Reasons, which have a one-to-many relationship (one SalesLine can have any number of (or zero) Reasons for having been modified). Now I want to create a report which shows a table with SalesLines and a measure for SUM(SalesQty) and also an additional column for corresponding Reasons (if there are any). I also want to be able to filter this table by Reason.

 

How to achieve this? At first I created a "normal" relationship between SalesLines and Reasons (with "filter both directions") but this leads to my report-table only showing rows that have a Reason. (I guess this is due to SSAS-relationships being of "LEFT OUTER JOIN"-character...). What I did to solve this was to prepare my Reasons-table (in SQL) by UNIONing on "no reason"-records for all SalesLines that don'd have a Reason. This kind of seems to work, but to me this solution is ugly, and it might cause potential problems in the future when wanting to use the Reasons-table for some other purpose where it's not OK that it contains "no reason"-reasons...

 

Can anyone guide me on how to solve this in a more elegant way?

2 REPLIES 2
Moderator v-qiuyu-msft
Moderator

Re: SSAS Tabular relationships problem (RIGHT OUTER JOIN equivalent)

Hi @Lasselakan,

 

Assume SalesLines table and Reasons table have a common field SalesLines, count distinct SalesLines values in SalesLines table should be more than one in Reasons table. Then you want to get a merged table which will list both SalesLines have reasons or not, right?

 

On Power BI Desktop side, you can use Merge Queries property to merge those two tables by Left outer Join.

 

q5.PNGq6.PNG

 

If you want to do it on SSAS side, I would suggest you post a thread in SSAS forum.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: SSAS Tabular relationships problem (RIGHT OUTER JOIN equivalent)

Click on the field (in the panel) and select Show items with no data (the actual name may be different).

 

Does it return the expected results?

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 88 members 1,267 guests
Please welcome our newest community members: