Possible Relationship Issue(?)

Power BI Data Model
1

In the attached PBIX file, I have created a simplified model of the database model used by our safety incident tracking system. The data is completely made up, and the model is vastly simplified from the actual model, but I hope it will help me explain the problem I’m having.

I have created two measures to illustrate the issue:

Total Type 2/3 using Type ID =
CALCULATE ( COUNTROWS ( VALUES ( 'Incident Type'[Incident ID] ) ), 'Incident Type'[Type ID] = 2 || 'Incident Type'[Type ID] = 3 )

Total Type 2/3 using Type.Type =
CALCULATE ( COUNTROWS ( VALUES ( 'Incident Type'[Incident ID] ) ), 'Incident Type'[Type.Type] = "Type 2" || 'Incident Type'[Type.Type] = "Type 3" )

The resulting stacked column charts look like this when I use the “Type.Type” column as the legend values:

Relationship Issue
Relationship Issue1307×737 39.3 KB

The bottom chart looks like there’s a missing or incorrect relationship in my model, but the part that has me stumped is that “Type ID” (the column used to filter the first measure), and “Type.Type” (the column used to filter the second measure) are coming from the same table. The only difference is that “Type ID” is native to the original table whereas “Type.Type” was added in PowerQuery using a merge operation with another table (you can see this transformation in the PBIX file).

Help? Why is it doing this?

Test Case 5.pbix (109.4 KB)

2

Try using filter in your measures. Its all about context. Though not sure if the Incident Table is a fact table or not, if it is you need to break that out so that you are not using Filter on a fact table because that could be slow.

Count = COUNTROWS( VALUES(‘Incident Type’ ))

Total Type 2/3 using Type ID with Filter = 
    CALCULATE( [Count]
        ,FILTER(  'Incident Type'
        ,'Incident Type'[Type ID] = 2 || 'Incident Type'[Type ID] = 3
    )
    )

Total Type 2/3 using Type.Type with Filter = 
    CALCULATE([Count]
        ,Filter( 'Incident Type'
        ,'Incident Type'[Type.Type] = "Type 2" || 'Incident Type'[Type.Type] = "Type 3"
    )
    )

image
image1327×736 37.4 KB

3

Thanks, @Nick_M! It worked! I can’t say that I completely understand why, so if you have any additional insight you can offer, that would be great!

Thanks for the quick reply!