Unable to combine 2 relations in one matrix

[Borja204]

Hi all,

I have been a long time trying to achieve the following and I have not be able to do it.

Here is the model:

The dimensions may change over time and each yearmonth may have a different value. Asking for help about that in this forum they told me to stablish the relations like this so I can filter the dimensions. Thats works perfectly fine.

Then to be able to calculate in a pivot table the sum by concept I have this measure in order to be able to use the inactive relation:

Sum Value (Concept) = CALCULATE(SUM(Data[Value]),USERELATIONSHIP(Concepts[ConceptCode],Data[ConceptCode]))

That works fine when all the dimensions I want to see in the pivot table are of the concepts table:

But, and here it comes the issue that is giving me a huge headache..... If I want to see in the pivot table Concept grops and then Country name below, I am unable to do it:

There appear all the countries, in fact I get it, they have no relation active at that moment. I have tried using the fiollowing measure but it trhows an error due to ambigous relation and I end at the beginning point again:

Sum Value (Concept) = CALCULATE(SUM(Data[Value]),
USERELATIONSHIP(Concepts[ConceptCode],Data[ConceptCode]),
USERELATIONSHIP(Countries[CountryIso],Data[CountryIso])
)

Edit: attached the wrong .pbix, here is the correct one:

PBIX File 

I delayed the implementation of this report and now I have a deadline soon to complete.

Any help is much apreciated,

Big thanks to all and regards,

[Borja204]

Any recomentadion on how to achieve it?

[v-jingzhang]

Hi @Borja204 

When one table filters another table, it is impossible to use two relationships at the same time. Power BI can only use one relationship to avoid ambiguous filtered result. If you want to activate the relationships 'Concepts' > 'Data' and 'Countries' > 'Data', there will be two active relationships between 'LoadReferences' and 'Data': 'LoadReferences' > 'Concepts' > 'Data' and 'LoadReferences' > 'Countries' > 'Data'. This is not allowed.

Although the three Dim tables 'Projects', 'Countries' and 'Concepts' are all changing over time, but I see they mainly change the names, while the relationships are built on IDs. If the same IDs represent the same objects regardless of what the names are, I suggest transforming the data to use only one name for each ID to reduce the number of time-changing Dim tables. Then modify the relationships between tables. Star schema is always recommended. At least the 'Countries' table could be switched to not time-changing as it will not change frequently.

Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Borja204]

Hi @v-jingzhang ,

Thanks for your help. The model that I posted here was a small piece representing the real model. It is true that some of the dimensions could be non-changing but in my real model there are like 3 dimensions that changes, some of them represent a hierarchy so it is not only the name and thats why I wanted to learn how to manage it. 

Indeed, one of the PowerBi projects repsesent only the last value of dimensions for each table (with a star schema as you suggested).

The problem comes with the other project (thats why I posted the model above) where I need to achieve the requeriment of being able to see the "reports" as they saw them one year ago. If they enter today to see a report in power bi with the dimensions of this year-month, they want the ability to select the year-month of the dimensions  in order to be able to re-build the same report theyre seeing today but in the future (and unfortunately it is not only the name, also hierarchies, I posted a simple representation here in order to get some help as I said)

Thanks for your assiatance anyways,

Regards!

[amitchandak]

@Borja204 , They are inactive because the model will not work with those. They are not alternate active.

I think you need to use SCD implementation as explained by Guyinacube in this video

https://www.youtube.com/watch?v=tKeaQpWynzg

[Borja204]

Hi again  @amitchandak ,

I saw the video but it seems the problem is not resolved there neither. The example that appears in that video is one with one userelationship, is not combining 2 tables to display in a visual, ot at least, I dont get it... 😕

[Borja204]

@amitchandak but there is no measure that can do that calc? like the use relation one above but for both tables