needing to pivot two columns that are in different tables - guidance needed

[hxkresl]

I need the count(Alerts[Alert number]) grouped by Sites[Country].

The two columns come from two different tables, distanced from each other by reversing 1:many relationships.

If I want to place count(Alert Numbers) and Country on same pivot table, what can I do to overcome the issue of the relationships?

columns needed from Alerts and Sites tables

[CahabaData]

If there is 1 country to an LOB it can be done.  But if an LOB is in multiple countries, as I would suspect - I don't think your data model can give an Alert count by country.  What I see is only an Alert by LOB ability....    sorry for a non PBI reply but was just looking at the tables.....

[Greg_Deckler]

That would depend on the issues you are having with the relationships. What are they? Generally when you find yourself in those kinds of situations you need to tinker with the cross-filtering settings of the relationships or refactor the data model. Power BI does a pretty fantastic job with transitive relationships in general. I've done models that have 3 or 4 intermediary tables that build a many:many relationships and Power BI purrs right along.