Forum Discussion

nelalx's avatar
nelalx
Frequent Visitor
3 years ago
Solved

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

I have three tables like above.  In this tables, the below relationships exist. soccermasterdata[RowKey] = country[RowKey]  soccermasterdata[RowKey] = devicedetails[Match]   Also country[Ro...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

     

nelalx's avatar
nelalx
Frequent Visitor
3 years ago

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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.