Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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.

Anonymous
Not applicable

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)

 

Anonymous
Not applicable

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
        )
    )
Anonymous
Not applicable

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 ) )
    )

 

Anonymous
Not applicable

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

AlexisOlson
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors