Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to implement a filter from two tables with AND condition to Invoiced Amount:
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?
Solved! Go to Solution.
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.
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 ) )
)
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.
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:
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.
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 ) )
)
Yes that is a much more elegant solution that covers the possibility of keys missing in the filter table! Thank you very much!
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |