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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.