topic Re: RELATED does not work despite one to one relationship? in DAX Commands and Tips

RELATED does not work despite one to one relationship?

Anonymous — Tue, 22 Jun 2021 15:23:07 GMT

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:

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?

Re: RELATED does not work despite one to one relationship?

AlexisOlson — Tue, 22 Jun 2021 15:38:35 GMT

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?

Re: RELATED does not work despite one to one relationship?

Anonymous — Wed, 23 Jun 2021 10:19:11 GMT

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'?

Re: RELATED does not work despite one to one relationship?

AlexisOlson — Wed, 23 Jun 2021 14:34:26 GMT

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.

Re: RELATED does not work despite one to one relationship?

Anonymous — Wed, 23 Jun 2021 15:10:02 GMT

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)

Re: RELATED does not work despite one to one relationship?

Anonymous — Wed, 23 Jun 2021 15:12:58 GMT

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]

)

Re: RELATED does not work despite one to one relationship?

AlexisOlson — Wed, 23 Jun 2021 15:27:36 GMT

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

Re: RELATED does not work despite one to one relationship?

Anonymous — Thu, 24 Jun 2021 10:25:00 GMT

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.

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

Re: RELATED does not work despite one to one relationship?

AlexisOlson — Thu, 24 Jun 2021 15:18:01 GMT

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

Re: RELATED does not work despite one to one relationship?

Anonymous — Fri, 25 Jun 2021 11:15:45 GMT

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