Power Bi Relationship

[G4analytics]

Hello Community, 

Is there any option to filter 2 fact tables by 2 dimension tables? The 2 fact tables have a Date column. My problem is im trying to do it but cant avoid the reduntance window.

I want to do it to prepare the same dimension slicers to filter both tables.

I appreciate your help,

Best regards,

G.

[DataSkills · Accepted solution]

I am still not all that clear on what you have done. A diagram of your relationships would be very helpful to understand what you are trying to explain. Why not take a screen shot of your relationship, and if possible, the unexpected results you are seeing?

I am also not clear on why you mention the date columns. What have these got to do with the dimensions as it seems that you have related

Dimension1.[ID] to FACT1.[Dim1ID] and FACT2.[Dim1ID] and...

Dimension2.[ID] to FACT1.[Dim2ID] and FACT2.[Dim2ID].

Where does the date field fit into all of this? 

However, you say that "...I don't find a way to filter for both dimensions". Are you saying that your fact tables don't filter the dimension tables? That is "normal" behaviour for a 1 to many relationship with a single cross filter direction (the default type of relationship in Power BI). Generally we want dimension tables to filter fact tables, but not the other way around. 

If you DO want the fact table to filter the dimension tables, you would need to change your cross filter direction to both. However, this can cause your model to be slower, and in more complex cases with multiple tables, can create ambiguity in your model. So, it's not generally desirable to do this. 

[v-linyulu-msft · Accepted solution]

Thanks for the reply from vanessafvg and  DataSkills , please allow me to provide another insight:

Hi, @G4analytics 

We think the main reason you have redundant data is because of a closed-loop relationship.
 

That's why we recommend that multiple fact tables join a dimension table to form a star chart structure:

I offer you two solutions:

First, you can try to modify the two active relationships of dimkey1 and dimkey2 to join the fct2 table to inactive, and then use USERELATIONSHIP to activate the relationship：

Here is a screenshot of the relevant documentation:

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

2.The second way is to copy dimkey1 and dimkey2 as dimkey11 and dimkey22 re-establish contact with fct1 and disconnect the original connection: dimkey1 and dimkey2 connect fct1.

Here is the relevant documentation:
Model relationships in Power BI Desktop - Power BI | Microsoft Learn
Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

The above is my suggestion, you can try it according to your needs, hopefully back up your data before trying.Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[vanessafvg]

just link the date dimension to both facts and use the date dimension to filter both facts.

not sure what you mean by the redundtance window?  you need the key from the dimension in both facts then link the dimension to both on the key and filter by the dimension?  or am i missing something?

[G4analytics]

Hello, sorry for that bad explanation of my problem, ill try to explain it better now.

I have 2 dimension tables called Description1 and Description2, only with 2 columns, the key and the description. 

I also have 2 fact tables which have the key to relate with both dimension tables and a Date column for each fact tables.

My problem is when i try to relate the 2 dimension to filter both fact tables and i dont find a way to filter for both dimensions.

Thank you

[vanessafvg]

you need to provide a screenshot of  the relationships you have set up

also please display how you are trying to filter on the page

[DataSkills · Accepted solution]

I am still not all that clear on what you have done. A diagram of your relationships would be very helpful to understand what you are trying to explain. Why not take a screen shot of your relationship, and if possible, the unexpected results you are seeing?

I am also not clear on why you mention the date columns. What have these got to do with the dimensions as it seems that you have related

Dimension1.[ID] to FACT1.[Dim1ID] and FACT2.[Dim1ID] and...

Dimension2.[ID] to FACT1.[Dim2ID] and FACT2.[Dim2ID].

Where does the date field fit into all of this? 

However, you say that "...I don't find a way to filter for both dimensions". Are you saying that your fact tables don't filter the dimension tables? That is "normal" behaviour for a 1 to many relationship with a single cross filter direction (the default type of relationship in Power BI). Generally we want dimension tables to filter fact tables, but not the other way around. 

If you DO want the fact table to filter the dimension tables, you would need to change your cross filter direction to both. However, this can cause your model to be slower, and in more complex cases with multiple tables, can create ambiguity in your model. So, it's not generally desirable to do this. 

[G4analytics]

Hello, thanks for your answers and sorry for my bad explanations.

This is more or less the structure im working on:

To clarify, the 2 fact tables in this example seems so similar but they have different columns with the real structure so i cant convert them in a single fact table.

I would like to be able to filter for key 1, key 2, key 3 and key 5 and being responsive and im not sure if it is the right way.

Best regards,

G.

[v-linyulu-msft · Accepted solution]

Thanks for the reply from vanessafvg and  DataSkills , please allow me to provide another insight:

Hi, @G4analytics 

We think the main reason you have redundant data is because of a closed-loop relationship.
 

That's why we recommend that multiple fact tables join a dimension table to form a star chart structure:

I offer you two solutions:

First, you can try to modify the two active relationships of dimkey1 and dimkey2 to join the fct2 table to inactive, and then use USERELATIONSHIP to activate the relationship：

Here is a screenshot of the relevant documentation:

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

2.The second way is to copy dimkey1 and dimkey2 as dimkey11 and dimkey22 re-establish contact with fct1 and disconnect the original connection: dimkey1 and dimkey2 connect fct1.

Here is the relevant documentation:
Model relationships in Power BI Desktop - Power BI | Microsoft Learn
Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

The above is my suggestion, you can try it according to your needs, hopefully back up your data before trying.Of course, if you have any new ideas, you are welcome to contact us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[G4analytics · Accepted solution]

Thank you for all your answers, very helpful.

[DataSkills]

Hello @G4analytics , you haven't described your issue very clearly. I suggest you review this article to get some guidance on how best to ask questions to help others understand your problem and answer you quickly. 

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...