Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nelalx
Regular Visitor

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

nelalx_1-1669068711054.png

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.

1 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.

 

 

View solution in original post

3 REPLIES 3
nelalx
Regular Visitor

@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. 

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
Super User
Super User

@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.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.