Join paths and relationship

[DominikTher]

Hello all, I have a simple model, with some relationships. If I don't restrict this in RLS everything works fine. But after that, I receive error message "Join paths ...". So the problem i think is in relationship between HubCountries and Countries - there is a both cross filter, but i need it. Also i try to make dynamic table with selected data from countries for example, but no succes.

Anybody help me with this issue? I'm also availible on google talk. Below are screenshots from my Power BI.

Thank you very much!

[v-ljerr-msft]

Hi @DominikTher,

---

---

So the problem i think is in relationship between HubCountries and Countries - there is a both cross filter, but i need it. Also i try to make dynamic table with selected data from countries for example, but no succes.

Yes, I also think the problem is in relationship between HubCountries and Countries, which needs to set to single in this scenario. 

Could you be more precisely with why you need the relationship between HubCountries and Countries to be both cross filter? So that we can try to help solve it. 

Regards

[DominikTher]

Hello @v-ljerr-msft,

Thanks for reply.

The problem why i need this relationship is that i use filtrers (Slicers) in my report, i need pre-filtered values. I'll show you example.

Period and RU tables are ok. Then, user select BU - after selection i need prefiltred Hubs which affected Countries which are in selected Hubs. And also BU directly affect PG because i need only PG's in BU's. 

I hope it's more clear now. If a remove both cross filter, the countries wil not be affected by Hubs because m:n. Maybe there is another solution how to do it. For example with DAX, but i'm a new with this.

[v-ljerr-msft]

Hi @DominikTher,

---

I hope it's more clear now. If a remove both cross filter, the countries wil not be affected by Hubs because m:n. Maybe there is another solution how to do it. For example with DAX, but i'm a new with this. 

Yes, I believe we can use DAX to create measure, then use it to apply a visual filter to filter the related countries in this scenario. Could you post some sample data and the expected result, so that I can help write the measure for you? It's better that you can share a sample pbix file(with just some sample/mock data) which can reproduce the issue. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Regards

[DominikTher]

Hello @v-ljerr-msft

I tried make simmilar model for demonstration. My real model is much bigger. So, there is a link to PBI file: https://file.town/download/nrwpmvf4mje3jn4kg4qoq4ilo

If you use role in this file and try to filter Hub you will see exactly same error which i posted here. In my real model i receive this error directly after i switch role (maybe beacuse i have a lot of data and connections) - but the issue is same.

If you have some tips, it would be nice 🙂

Thank you, Dominik

[v-ljerr-msft · Accepted solution]

Hi @DominikTher,

Based on my test with your shared pbix file, you should be able to use the formula below to create a new measure to indicate if the Data[CountryId] is in filtered Hub, then use it to apply a visual level filter(Measure 2 is 1) on the Table visual in your scenario.

Measure 2 =
IF (
    CONTAINS ( HubCountry, HubCountry[CountryId], MAX ( Data[CountryId] ) ),
    1,
    0
)

Here is the modified pbix file for your reference. 

Regards

[DominikTher]

Hello @v-ljerr-msft, thanks for your solution.

Hope that someday i find solution how to get also prefiltred countries. Beacause we have lot of data and countries.

But right now your solution is very very helpful. And answer as is are acceptable 🙂 

Thank's

And if there will be solution for Countries i post it back.

Dominik

[greatzt]

@DominikTher I have got the same problem you had.  The soluation in the thread is good to filter tables. However, the country filter is not prefiilterd. Have you found a solution? Thanks!