Power BI filter based on a relationship which i cannot make active due to ambiguity error.

[nelalx]

I have three tables like above.  In this tables, the below relationships exist.

soccermasterdata[RowKey] = country[RowKey] 

soccermasterdata[RowKey] = devicedetails[Match]

Also country[RowKey] = devicedeatils[Match] . I have a slicer which uses the country field from the country coloum. The field , viewership minutes from soccermasterdata is being filtered properly, but when i try to take the unique count of deviceid from devicedetails table, it is not working. 

Can you please help me with this? Thanks in advance.

[nelalx]

@AllisonKennedy Thanks for the response. The soccermaster contains data for some football matches and country is a reference table of viewers from differnt countries. So against a match in soccermaster, there are multiple countries from where viewership is generated. Now devicedetails have all unique deviceid's who contributed to viewership and i have added a country field there as well hoping that it will be useful to match. In the visual, i have a slicer which uses the country field, which is filtering the viwerminutes from soccwermaster data, but i am not able to find the count of deviceid's by country. Hope this explains the scenario. 

[v-rzhou-msft · Accepted solution]

Hi @nelalx ,

I think this issue should be caused by many to many relationship between country[RowKey] and devicedeatils[Match].

Here I suggest you to create a DimRowKey table between country and devicedeatils. 

You can refer to this offical blog:Apply many-to-many relationships in Power BI Desktop to learn more details and the workarounds.

Or you can create a measure to calcualte the resuly you want by the filter as below.

Count =
CALCULATE (
    COUNT ( devicedeatils[DeviceId] ),
    FILTER ( devicedeatils, devicedeatils[Match] = MAX ( country[RowKey] ) )
)

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AllisonKennedy]

@nelalx  Can you try to explain in english how these three table are related? You should try to use 1 to many single cross filter direction. Both your relationships are both cross filter direction which is dangerous and ambiguous. Also the many to many relationship complicates things.