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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.