error: column that contains many values without specifying an aggregation such as min, max, count

[vally57]

HI Team,

I have created a meaure
IF (ISBLANK(('table A'[Customer])) || ISBLANK(('table A'[Delivery No])) || ISBLANK(('table A'[Check-In Date Adjusted])) || ISBLANK(('table B'[Ship Date])) ,blank(),
if( ('table A'[Check-In Date Adjusted]) - ( 'table B'[Ship Date])<= 0,1,0))

there is a many-to-one relationship among table A and table B.

I'm getting an error like below: A single value for column 'Check-In Date Adjusted' in table 'table A' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

can anyone help me on changing this dax measure, I do not want to use any aggregate function here in last line as it does not get the expected result

[123abc]

The error you're encountering typically arises because Power BI requires aggregation for measures to ensure that it can produce a single value in the context of the visualizations you're creating. However, in your case, it seems like you're trying to perform row-level calculations based on conditions without necessarily aggregating data.

Your measure is trying to compare values between rows in 'table A' and 'table B'. The issue likely stems from the fact that the measure doesn't know which row to consider when it's comparing 'table A'[Check-In Date Adjusted] with 'table B'[Ship Date].

To address this issue without using aggregate functions, you might consider iterating over rows in 'table B' based on the context of 'table A'. One way to achieve this is by using iterators like FILTER or RELATEDTABLE. Here's how you might rewrite your measure:

YourMeasure =
IF (
ISBLANK ( 'table A'[Customer] )
|| ISBLANK ( 'table A'[Delivery No] )
|| ISBLANK ( 'table A'[Check-In Date Adjusted] )
|| ISBLANK ( 'table B'[Ship Date] ),
BLANK (),
VAR ShipDate =
CALCULATE (
MAX ( 'table B'[Ship Date] ),
FILTER (
'table B',
'table A'[Delivery No] = 'table B'[Delivery No]
&& 'table A'[Customer] = 'table B'[Customer]
)
)
RETURN
IF (
'table A'[Check-In Date Adjusted] - ShipDate <= 0,
1,
0
)
)

In this measure, I used CALCULATE along with FILTER to retrieve the 'Ship Date' from 'table B' based on the matching conditions from 'table A'. This ensures that you're getting a single value to compare with 'Check-In Date Adjusted' in 'table A'. The MAX function is used to ensure that only one value is returned in case there are multiple matching rows.

Please adjust the column names and conditions according to your data model as needed. This approach should help you perform row-level calculations without resorting to aggregation functions directly.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[vally57]

I'm getting the same error: A single value for column 'Delivery No in table 'Table A' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[123abc]

If you're encountering the same error even after using an iterator like SUMX, it suggests that the issue might be related to the context in which the measure is being evaluated or the relationships between tables.

Here's another approach you might try to address the issue:

New Measure =
IF (
ISBLANK ( 'table A'[Customer] )
|| ISBLANK ( 'table A'[Delivery No] )
|| ISBLANK ( 'table A'[Check-In Date Adjusted] )
|| ISBLANK ( 'table B'[Ship Date] ),
BLANK (),
CALCULATE (
COUNTROWS ( 'table A' ),
FILTER (
'table A',
'table A'[Check-In Date Adjusted] - RELATED ( 'table B'[Ship Date] ) <= 0
)
)
)

In this approach:

• We use the CALCULATE function to modify the filter context for the COUNTROWS function.
• Within the FILTER function, we specify the condition based on the relationship between 'table A' and 'table B'.
• We count the rows of 'table A' where the condition is met.

This should help ensure that the measure returns a single value for each context, and it should help resolve the error you're encountering.

If you're still encountering issues, you might need to review the relationships between your tables and the context in which the measure is being evaluated to ensure they align with your expectations.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.