Help with related tables - more that one connection

[pasno]

Hi guys,

I'm stuck in a problem with related table, could you please help me with the example below?

I have:

- One calendar table that I use to filter all my values

- One table with direct costs for each customer

- One table with indirect costs for each customer

I'd like to put in a table a value that is direct + idirect costs splitted by month so I made some connections between my tables as below:

- Connection between costs table with the key: Customer ID

- I need to link calendar table to both costs table but I can't because I cannot have more that one active connection!

So with this problem my results are not correct because PBI does not sum considering date.

Any idea please?

Please find also, in the link below, my PBI sample file.

https://www.dropbox.com/t/mQQCkpCjKlUtB3ZX

Thank you very much!

[v-nuoc-msft · Accepted solution]

Hi @pasno 

Power BI does not allow multiple active relationships between the same tables, but you can work around this limitation using inactive relationships and DAX measures.

For your question, here is the method I provided:

Here is the data you provided

"Direct costs"

"Indirect costs"

The relationship between Direct costs and Calendar is active. Create a measure to calculate direct costs, for tables with inactive relationships, you can use the "USERELATIONSHIP" function in the DAX measure for calculate indirect costs.

Here is the result

RESULT = 
    VAR DIRECT_VALUE = 
        SUM(
            'Direct costs'[Value]
        )
    VAR Indirect_VALUE = 
        CALCULATE(
            SUM(
                'Indirect costs'[Value]
            ), 
            USERELATIONSHIP(
                'Calendar'[Data], 
                'Indirect costs'[Date]
            )
        )
RETURN 
    IF(
        ISFILTERED('Calendar'[Data]), 
        DIRECT_VALUE + Indirect_VALUE, 
        BLANK()
    )

Regards,

Nono Chen

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

[audreygerred]

Hello! Instead of connecting your two cost table together, create a table that has customer ID and any other dimensions related to the customer that you want. Join the Date table to Direct Costs and Indirect Costs, then join the newly added ID table to Direct Costs and Indiect Costs.