SSAS Tabular relationships problem (RIGHT OUTER JOIN equivalent)

[Anonymous]

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?

[LaurentCouartou]

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?

[v-qiuyu-msft]

Hi @Anonymous,

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.

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

Best Regards,
Qiuyun Yu