cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kjhertz_ikea
Helper I
Helper I

RELATED does not work despite one to one relationship?

I am trying to implement a filter from two tables with AND condition to Invoiced Amount:

 
Retail Invoiced Amount =
CALCULATE(
[Invoiced Amount EUR],
'Legal Unit Filter'[Procurement KPI]="Retail KPI",
FILTER(
'Business Unit',
RELATED('Business Unit Filter'[Procurement G&SF.H&T KPI ])<>"Yes"
)
)

 

But Power BI gives me an error:
The column 'Business Unit Filter[Procurement G&SF.H&T KPI ]' either doesn't exist or doesn't have a relationship to any table available in the current context.

This is strange since Business Unit and Business Unit Filter tables have a One-to-one relationship:

 

kjhertz_ikea_1-1624375145119.png

Business Unit then has a One-to-Many relationship with the Invoice table.

 

What can be the problem here? When I try the same setup in a test file with same set of table relationships there is no error and Invoice Amount is calculated correct. I am working in a composit model could that effect how RELATED works?

 

 

2 ACCEPTED SOLUTIONS

Ah, sorry. I did miss that Filter part of the name.

I think the issue may indeed be that the relationship is weak (a.k.a a limited relationship) since it's a cross-island relationship between different data storage types. In the documentation for RELATED, it explicitly says

  • The RELATED function cannot be used to fetch a column across a limited relationship.

View solution in original post

This is indeed an important distinction. I'd tweak my second example above to give something I think is simpler to read than your EXCEPT version (though the logic is equivalent):

 

Sum Amount EXCEPT A =
VAR IDs_A =
    CALCULATETABLE ( VALUES ( 'Filter Table'[ID] ), 'Filter Table'[ID] = "A" )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( VALUES ( 'Table'[ID] ), NOT ( 'Table'[ID] IN IDs_A ) )
    )

 

View solution in original post

9 REPLIES 9
kjhertz_ikea
Helper I
Helper I

Thank you for the reply. Note that the 'Business Unit Filter'[Procurement G&SF.H&T KPI ] column is not in the 'Business Unit' table but the 'Business Unit Filter' table thus the use of RELATED. 

If I remove the RELATED I get an error saying a single value for 'Business Unit Filter'[Procurement G&SF.H&T KPI] cannot be determined which is strange since the connection is One-To-One a single value in the columns 'Business Unit Filter'[Procurement G&SF.H&T KPI] should be available for each row in 'Business Unit'?

Ah, sorry. I did miss that Filter part of the name.

I think the issue may indeed be that the relationship is weak (a.k.a a limited relationship) since it's a cross-island relationship between different data storage types. In the documentation for RELATED, it explicitly says

  • The RELATED function cannot be used to fetch a column across a limited relationship.

View solution in original post

Yes that is it! The model is Composite with 'Legal Unit Filter' table from different source than 'Legal Unit' table thus making the relationship weak according to the documentaiton:

 

A model relationship is limited when there's no guaranteed "one" side. It can be the case for two reasons:

  • The relationship uses a Many-to-many cardinality type (even if one or both columns contain unique values)
  • The relationship is cross source group (which can only ever be the case for Composite models)

 

I was able to solve this by using EXCEPT. If anyone knows a better way to get around this weak/limite relationship in Composit models please let me know!
 
#RetailInvoicedAmount =
CALCULATE(
[Invoiced Amount EUR],
'Legal Unit Filter'[Procurement KPI]="Retail KPI",
EXCEPT(
SELECTCOLUMNS(
'Business Unit',
"ID",
'Business Unit'[Business Unit ID]),
SELECTCOLUMNS(
FILTER(
'Business Unit Filter',
'Business Unit Filter'[Procurement G&SF.H&T KPI ]="Yes"
),
"ID",
'Business Unit Filter'[Business Unit ID]
)
)

Does this simpler version work?

Retail Invoiced Amount =
CALCULATE (
    [Invoiced Amount EUR],
    'Legal Unit Filter'[Procurement KPI] = "Retail KPI",
    FILTER (
        'Business Unit Filter',
        'Business Unit Filter'[Procurement G&SF.H&T KPI ] <> "Yes"
    )
)

 

If not, then I'd probably try something along these lines:

Retail Invoiced Amount =
VAR BUF_Ids =
    CALCULATETABLE (
        VALUES ( 'Business Unit Filter'[Business Unit ID] ),
        'Business Unit Filter'[Procurement G&SF.H&T KPI ] <> "Yes"
    )
RETURN
    CALCULATE (
        [Invoiced Amount EUR],
        'Legal Unit Filter'[Procurement KPI] = "Retail KPI",
        FILTER (
            VALUES ( 'Business Unit'[Business Unit ID] ),
            'Business Unit'[Business Unit ID] IN BUF_Ids
        )
    )

Thank you for taking the time to help me understand this. And yes the first one gives the result I need in a more straightforward way 🙂

 

But I am worried the FILTER solution could inadvertly remove business units from the Amount if they are not present in the Business Unit Filter table. I have illustrated this in an simpler example below with both a FILTER solution and an EXCEPT solution. Perhaps the FILTER behaivor changes when the connection is Limited/Weak? Edit: In the example below adding zero rows to create Many-To-Many did not change the result.

 

The correct amount in this case should be 20. 

 

kjhertz_ikea_2-1624530263812.png

Edit again: EXCEPT should be = A not <>A.

 

 

 

 

This is indeed an important distinction. I'd tweak my second example above to give something I think is simpler to read than your EXCEPT version (though the logic is equivalent):

 

Sum Amount EXCEPT A =
VAR IDs_A =
    CALCULATETABLE ( VALUES ( 'Filter Table'[ID] ), 'Filter Table'[ID] = "A" )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER ( VALUES ( 'Table'[ID] ), NOT ( 'Table'[ID] IN IDs_A ) )
    )

 

View solution in original post

Yes that is a much more elegant solution that covers the possibility of keys missing in the filter table! Thank you very much!

AlexisOlson
Community Champion
Community Champion

When you are filtering the table 'Business Unit', you don't need to use RELATED to access the columns of that same table. That doesn't make sense.

 

What happens if you remove RELATED on that column?

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors