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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.