Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 # | Date | Type | Duration | Duration Value | Setting | Condition |
123 | 2021/01/03 | Measurement A | 100515 | TRUE | FALSE | Level 5 |
124 | 2021/01/03 | Measurement A | 64721 | FALSE | FALSE | None |
127 | 2021/01/04 | Measurement A | 45007 | FALSE | FALSE | Level 3 |
129 | 2021/01/04 | Measurement A | 98222 | TRUE | TRUE | None |
130 | 2021/01/05 | Measurement A | 32400 | FALSE | FALSE | None |
Table #2
Record# | Date | Type | Duration | Duration Value |
124 | 2021/01/03 | Measurement B | 97201 | TRUE |
129 | 2021/01/04 | Measurement B | 126050 | TRUE |
130 | 2021/01/05 | Measurement B | 32400 | FALSE |
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 # | Date | Type | Duration | Duration Value | Setting | Condition |
123 | 2021/01/03 | Measurement A | 100515 | TRUE | FALSE | Level 5 |
124 | 2021/01/03 | Measurement A | 64721 | FALSE | FALSE | None |
124 | 2021/01/03 | Measurement B | 97201 | TRUE | FALSE | None |
127 | 2021/01/04 | Measurement A | 45007 | FALSE | FALSE | Level 3 |
129 | 2021/01/04 | Measurement B | 126050 | TRUE | TRUE | None |
129 | 2021/01/04 | Measurement A | 98222 | TRUE | TRUE | None |
130 | 2021/01/05 | Measurement A | 32400 | FALSE | FALSE | None |
130 | 2021/01/05 | Measurement B | 32400 | FALSE | FALSE | None |
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:
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.
Solved! Go to Solution.
@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.
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
@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.
Respectfully,
Zoe Douglas (DataZoe)
Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/
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.
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).
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |