How can I enforce (1,n) relationship

[Anonymous]

Hello folks,

Is it possible to enforce a (1,n) relationship in Power BI ?  I've tried several times to manually setup the relationship (1,n) between dimension and fact tables and each time after saving Power BI revert it to (n,1)

Case : I have a dimensional table that contains 10 channel values and business requires to add Buttons filters that should show only shows values for 4,6 or 10 channels.

I know the standard way of dealing with such a case is to use bookmarks and replicate 3 times the scenario for each button.  However I have too much data & visuals & filters and I do not want to copy multiple times the same thing.

So I loaded two additional times the dimensional table in order to get  3 tables with resp 4, 6 ad 10 channels and I'd like to rely on (1,n) relationships in order to filter out the lines that are not matched. However Power BI disregard the (1,n) relationship and each time converts it to (n,1). Even if  it was possible to enable the cross filter direction option ( which I can't because it could be active for one only table and not two), it still shows me the blank values for the missing channeld from the dimensional table with 4 or 6 channels.

Can someone tell me what I'm missing? Why the filtering via the relationship is not working ? Is there some additional setup to be done?

Best regards,

Pavel

[lbendlin]

How is adding more tables better than adding bookmarks/buttons?

Might be easier to teach your business users how to operate the filter pane, and how to create their own bookmarks or use your report bookmarks (even without buttons).

If your dimension are not covering the fact table you need to specifically exclude blanks.

[Anonymous]

Having smaller dimension table, that matches the need of specific filter seems to me better than have duplicates of visuals in order to assign buttons/bookmarks.
Also my expectations ( maybe incorrect ones) are that when I make a (1,n) relationship between Dimension & Fact table, I should be seeing only the inner joined data, i.e. what is not present in one of tables should not be visible as blanks.

[lbendlin · Accepted solution]

In Power BI the default behavior is similar to right outer join. Especially when your dimension data is unclean ( ie not covering). Read about referential integrity assumptions.