cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
obera
Frequent Visitor

1:1 Relationship Cross-filtering Visuals

Hi All,

This may be a case of 'I looked at it so long my eyes are crossed' and I'm missing something simple, but I'm missing it, so here I am. I have a slightly unusual data model. I will try to give a descriptive example with dummy data:

 

Table#1

Record #DateTypeDurationDuration ValueSettingCondition
1232021/01/03Measurement A100515TRUEFALSELevel 5
1242021/01/03Measurement A64721FALSEFALSENone
1272021/01/04Measurement A45007FALSEFALSELevel 3
1292021/01/04Measurement A98222TRUETRUENone
1302021/01/05Measurement A32400FALSEFALSENone

 

Table #2

Record#DateTypeDurationDuration Value
1242021/01/03Measurement B97201TRUE
1292021/01/04Measurement B126050TRUE
1302021/01/05Measurement B32400FALSE

 

Table #1 is the one I treat like my fact table in my data model. Almost all other tables connect to it, including Table#2. Both Table 1 & 2 have no duplicates for Record# (this is expected), so when I create a relationship between the two it is 1:1 and has filtering set for both ways.

 

I have created a DAX measure using the data from Table#2: % Met M2 = DIVIDE(COUNTROWS(FILTER('Table#2', 'Table#2'[Value1] = FALSE())), COUNTROWS('Table#2'))

However, I am unable to filter that measure or any other data measures for Table#2 in visuals based on fields I have in other tables, including the 'Setting' field which I only have in Table#1. I can't figure out why this doesn't work, when I can filter based on other relationships. I tried to read the MS documentation on relationships and filtering and 1:1 relationships were platered in warnings but I couldn't figure out if this is expected behavior or not. The documentation I looked at states that you should avoid 1:1 relationships if possible and just pull the information from your source in one table. Except in my case, the source data has a separate entry for each Measurement Type, so it would look like this:

Record #DateTypeDurationDuration ValueSettingCondition
1232021/01/03Measurement A100515TRUEFALSELevel 5
1242021/01/03Measurement A64721FALSEFALSENone
1242021/01/03Measurement B97201TRUEFALSENone
1272021/01/04Measurement A45007FALSEFALSELevel 3
1292021/01/04Measurement B126050TRUETRUENone
1292021/01/04Measurement A98222TRUETRUENone
1302021/01/05Measurement A32400FALSEFALSENone
1302021/01/05Measurement B32400FALSEFALSENone

 

This is no good for me, especially since I need a step to remove duplicates (expected, sometimes there can be two entries for Measurement A depending on the record, but we only want to use one). So I separate out Measurement A & B when exporting at the source to create separate tables. Everything seems to me like it should work, but I am getting bad results all over the place. The two that stand out to me are:

  1. Records that show up in visualizations without a Date even though when I check both the Date table and the record, the Date exists. (The Date table is connected to Table#1 but not Table#2, and this is only SOME records, not all, and not even consistantly the same date?)
  2. Unable to filter the visuals based on Setting field, even though based on my experience with *:1 or 1:* relationships, this should be possible.

Any insight on this would be appreciated. I tried searching generally for the answer to this but my top results are all sites that are just quoting the MS product info or questions that are not related to mine. I may be using incorrect search terminology. Thanks for your time.

1 ACCEPTED SOLUTION
DataZoe
Super User II
Super User II

@obera For the relationship between Table 1 and Table 2 you can override the default behavior (which is happening becuase it sees they are both unique) and set it to "1 to Many" (Table 2 as the 1 side, and Table 1 as the many side), and then set it so Table 2 filters Table 1 in a single direction. 

DataZoe_0-1618533717017.png

 

DataZoe_1-1618533751180.png

 

 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

3 REPLIES 3
DataZoe
Super User II
Super User II

@obera For the relationship between Table 1 and Table 2 you can override the default behavior (which is happening becuase it sees they are both unique) and set it to "1 to Many" (Table 2 as the 1 side, and Table 1 as the many side), and then set it so Table 2 filters Table 1 in a single direction. 

DataZoe_0-1618533717017.png

 

DataZoe_1-1618533751180.png

 

 

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

View solution in original post

obera
Frequent Visitor

Thank you! I don't know why this didn't occur to me. I'm pretty sure this solved it for me - I'm still having date issues, but the more I look at it the more I think that is something separate. Filtering is now working as I need it to.

daxer
Solution Sage
Solution Sage

@obera 

 

If you have 1-to-1 relationships, this means you should change your model. The tables with 1-to-1 are really just one table. Consolidate the tables into one. That something is possible does not mean you should do it. Your relationships should be 1-to-many 99% of the time. And your model should be a star-schema (Understand star schema and the importance for Power BI - Power BI | Microsoft Docs).

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors