Forum Discussion
DATEADD using EARLIER function
- Anonymous1 year ago
Hi,willb006 ,thank you for your reply.
According to your latest information, you need to realize the effect of grouping calculation based on TicketID on the original basis, then you just need to change all the places in the code where ALL('Table') is used to ALLEXCEPT('Table'['ticket_number']), and let the original scope of the filter table which removes all the external filters be changed to just affected by the grouping of ['ticket_number'].
to achieve the effect of grouping (each ticket_number is counted internally).
like this:result_column02 = VAR _date = [comment_sort] VAR _numnotbalnk = CALCULATE ( MAX ( 'Table'[comment_sort] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ticket_number] ), 'Table'[comment_sort] < _date && NOT ( ISBLANK ( [wall_commentcreatedon] ) ) ) ) VAR diffday = CALCULATE ( SUM ( 'Table'[Days] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ticket_number] ), 'Table'[comment_sort] > _numnotbalnk && 'Table'[comment_sort] <= _date ) ) VAR lastdate1 = CALCULATE ( MAX ( 'Table'[wall_commentcreatedon] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[ticket_number] ), 'Table'[comment_sort] = _numnotbalnk ) ) RETURN IF ( ISBLANK ( 'Table'[wall_commentcreatedon] ), lastdate1 + diffday, 'Table'[wall_commentcreatedon] )Note that your data is quite specific and implements not really iterative logic (it looks like iteration, but it is not).
And fortunately, the source you gave has a comment_sort column to achieve an iteration-like effect (actually a conditional judgment based on sortnum, where each row is recalculated from the first row). If you don't provide a column for comment_sort, everything will get very tricky. (DAX itself doesn't support implementing iteration (it calculates uniformly based on a whole column, not on each cell, and there is no looping logic), and if the implementation wants true iteration logic, it's usually handled locally in the datasource or through Power Query using looping functions.
I hope my explanation can answer your questions.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jtian-msft thank you for this. Would it be iterative if I had more than one Ticket number? I tried your solution and I had to do it like this "
| ticket_number | wall_comment | wall_commentcreatedon | comment_sort | Days | Expected Output |
| 6212482 | PENDING (CUST) PA REQUEST SUBMISSION - Assigned. Assigned Role - Licensee. | 6/8/2024 0:00 | 1 | 10 | 6/8/2024 |
| 6212482 | PENDING SURVEY - Assigned. Assigned Role - Owner. | 6/17/2024 0:00 | 2 | 40 | 6/17/2024 |
| 6212482 | PENDING (CUST) REVIEW - Assigned. Assigned Role - Licensee. | 9/4/2024 0:00 | 3 | 10 | 9/4/2024 |
| 6212482 | PENDING (DOM) REVIEW - Assigned. Assigned Role - Owner. | 4 | 10 | 9/14/2024 | |
| 6212482 | PENDING (DOM) DESIGN - Assigned. Assigned Role - Owner. | 9/6/2024 0:00 | 5 | 10 | 9/6/2024 |
| 6212482 | PENDING (CUST) PAYMENT - Assigned. Assigned Role - Owner. | 6 | 10 | 9/16/2024 | |
| 6212482 | PENDING Make-Ready - Assigned. Assigned Role - Owner. | 7 | 135 | 1/29/2025 | |
| 4979490 | PENDING (CUST) PA REQUEST SUBMISSION - Assigned. Assigned Role - Licensee. | 1/26/2022 0:00 | 1 | 10 | 1/26/2022 |
| 4979490 | PENDING SURVEY - Assigned. Assigned Role - Owner. | 5/8/2023 0:00 | 2 | 40 | 5/8/2023 |
| 4979490 | PENDING (CUST) REVIEW - Assigned. Assigned Role - Licensee. | 3 | 10 | 6/17/2023 | |
| 4979490 | PENDING (DOM) REVIEW - Assigned. Assigned Role - Owner. | 4 | 10 | 6/27/2023 | |
| 4979490 | PENDING (DOM) DESIGN - Assigned. Assigned Role - Owner. | 5 | 10 | 7/7/2023 | |
| 4979490 | PENDING (CUST) PAYMENT - Assigned. Assigned Role - Owner. | 6 | 10 | 7/17/2023 | |
| 4979490 | PENDING Make-Ready - Assigned. Assigned Role - Owner. | 5/8/2023 0:00 | 7 | 135 | 5/8/2023 |
| 4979490 | PENDING GRANTED - Assigned. Assigned Role - Owner. | 10/11/2023 0:00 | 8 | 1 | 10/11/2023 |
| 4979490 | PENDING (CUST) PA WORK COMPLETE - Assigned. Assigned Role - Licensee. | 10/11/2023 0:00 | 9 | 1 | 10/11/2023 |
| 4979490 | PENDING VALIDATE - Assigned. Assigned Role - Owner. | 8/26/2024 0:00 | 10 | 1 | 8/26/2024 |
| 4979490 | PENDING (CUST) VIOLATION REPAIR - Assigned. Assigned Role - Licensee. | 9/5/2024 0:00 | 11 | 1 | 9/5/2024 |
Hi,willb006 ,thank you for your reply.
According to your latest information, you need to realize the effect of grouping calculation based on TicketID on the original basis, then you just need to change all the places in the code where ALL('Table') is used to ALLEXCEPT('Table'['ticket_number']), and let the original scope of the filter table which removes all the external filters be changed to just affected by the grouping of ['ticket_number'].
to achieve the effect of grouping (each ticket_number is counted internally).
like this:
result_column02 =
VAR _date = [comment_sort]
VAR _numnotbalnk =
CALCULATE (
MAX ( 'Table'[comment_sort] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ticket_number] ),
'Table'[comment_sort] < _date
&& NOT ( ISBLANK ( [wall_commentcreatedon] ) )
)
)
VAR diffday =
CALCULATE (
SUM ( 'Table'[Days] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ticket_number] ),
'Table'[comment_sort] > _numnotbalnk
&& 'Table'[comment_sort] <= _date
)
)
VAR lastdate1 =
CALCULATE (
MAX ( 'Table'[wall_commentcreatedon] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ticket_number] ),
'Table'[comment_sort] = _numnotbalnk
)
)
RETURN
IF (
ISBLANK ( 'Table'[wall_commentcreatedon] ),
lastdate1 + diffday,
'Table'[wall_commentcreatedon]
)
Note that your data is quite specific and implements not really iterative logic (it looks like iteration, but it is not).
And fortunately, the source you gave has a comment_sort column to achieve an iteration-like effect (actually a conditional judgment based on sortnum, where each row is recalculated from the first row). If you don't provide a column for comment_sort, everything will get very tricky. (DAX itself doesn't support implementing iteration (it calculates uniformly based on a whole column, not on each cell, and there is no looping logic), and if the implementation wants true iteration logic, it's usually handled locally in the datasource or through Power Query using looping functions.
I hope my explanation can answer your questions.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- willb0061 year agoRegular Visitor
Makes sense. I was using th all except but only on the last step. Now I am getting this error "A single value for column 'wall_commentcreatedon' in table 'Append1' 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." Append1 is my table name
- Anonymous1 year agoNot applicable
Hi,willb006 .Thank you for your reply.
Based on your description. I think the problem should be here:Normally in the CALCULATE function, the expected value of the calculation should be an aggregated value (a single value, not just a column). This is because the CALCULATE function performs the calculation in the specified context, and if the returned result contains multiple values, DAX will not be able to determine what to do with them, resulting in an error.
The positions in the red area in the screenshot above should all be calculated using an aggregation function that returns a scalar value (i.e., a single value)
Location of the error code that I think might be wrong:
VAR lastdate1 = CALCULATE ( 'Table'[wall_commentcreatedon] , FILTER ( ALLEXCEPT ( 'Table', 'Table'[ticket_number] ), 'Table'[comment_sort] = _numnotbalnk ) )change to this:
CALCULATE ( MAX ( 'Table'[wall_commentcreatedon] ),// use MAX function FILTER ( ALLEXCEPT ( 'Table', 'Table'[ticket_number] ), 'Table'[comment_sort] = _numnotbalnk ) )
I have uploaded the complete pbix file, you can view the file and do a complete error check against the code above, I have not used any other meausre in the entire calculation and there are no other table relationships and the final results are displayed properly so I think you may have missed something in the code writing process (in the calculate function, the first parameter in the calculate function must be an aggregate value, not a column name with multiple values), good luck figuring out what's wrong.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.- willb0061 year agoRegular Visitor
I appreciate all you help with this v-jtian-msft the issuse is in the last line of code
VAR lastdate1 =CALCULATE (MAX ( 'Query1'[wall_commentcreatedon] ),FILTER (ALLEXCEPT ( 'Query1', 'Query1'[ticket_number] ),'Query1'[comment_sort] = _numnotblank))RETURNIF (ISBLANK ('Query1'[wall_commentcreatedon]),lastdate1 + diffday,'Query1'[wall_commentcreatedon])this is looking for an aggregate function. I tried both MIN and MAX but that doesn't work because it's taking the MAX for the ticket number. Would a filter with allexcept comment sort work since that is the unique value?