Forum Discussion

JOHNPINTOC's avatar
JOHNPINTOC
Regular Visitor
6 years ago
Solved

Two active relationships between different tables (UniqueID and Dates)

Good Morning, I am new to PowerBi and therefore probably this will be a simple question, I just did not find quite the answer I was looking for:

I have three (main tables that I want to relate and use them in cross filtering in my report:
Monthlyprod (Data Table)
-Date (Volume_Date related to dates table)
-Item_ID (UniqueID related to headerID table)
-Numeric Values (several fields)

PORTAFOLIO_M (Data Table)

- Date( Volume_Date related to dates table)

-Item_ID (UniqueID from headerID table)

-Portafolio_ID (related to another Portafoli_ID table with unique values)

-Numeric values (several fields)

 

HeaderID (Table with unique Item_ID)

-Item_ID (uniqueID, it is unique along the table)

 

Date Table (classical date table)

-Date field, etc

 

I would like to be able to cross filter in my report for both dates and UniqueID, I madeit for Portafolio table but not for Monthlyprod table, for some reason in table monthlyprod ony one relationship should be active wheras in portafolio_m table powerbi allowed me to have both fields "active" as relationships..

PS.: I guess my bottom-line question is : why am I allowed to have multiple active relationships with different tables in Portafolio_M table and not on Monthlyprod table which are esentially the same in nature (the first one is future whereas the last one is past production), but again, its nature is the same : some numeric values per Item_ID and per Date....

Any Ideas? 

Thanks in advance.....Tables Relationships

 

 
 
  • Hi JOHNPINTOC 

     

    The reason is simple, it's because of bidirectional relationships.
    Active relationship: Headerid <> Monthlyprod 

     

    What would happen if you could create a relationship between Dates and Monthlyprod:
    Headerid  <> Portafoli_m <> Dates  <> Monthlyprod 

     

    Monthlyprod is currently filtered by Headerid and Headerid filters Portafoli_m that filters Date so you can not create a relationship with Dates because it would mean Headerid has  two active relationships with Monthlyprod 

     

    Solutions:

    You can change the relationship between Portafoli_m and  Dates to the single direction where Dates filters Portafoli_m.

    Or adhere to best practice and avoid bidirectional relationships altogether.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

2 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion

    Hi JOHNPINTOC 

     

    The reason is simple, it's because of bidirectional relationships.
    Active relationship: Headerid <> Monthlyprod 

     

    What would happen if you could create a relationship between Dates and Monthlyprod:
    Headerid  <> Portafoli_m <> Dates  <> Monthlyprod 

     

    Monthlyprod is currently filtered by Headerid and Headerid filters Portafoli_m that filters Date so you can not create a relationship with Dates because it would mean Headerid has  two active relationships with Monthlyprod 

     

    Solutions:

    You can change the relationship between Portafoli_m and  Dates to the single direction where Dates filters Portafoli_m.

    Or adhere to best practice and avoid bidirectional relationships altogether.

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn