Forum Discussion
"Grouping Issue"
Dear all,
i hope someone can help me with the current problem i have.
I have the following dax calculated column which will retunr a speficic status
AllocationStatus =
VAR CurrentLine = OrderItemData[LINE]
VAR TotalLineDetails = CALCULATE(COUNTROWS(OrderItemData), OrderItemData[LINE] = CurrentLine)
VAR SameDateFlag = OrderItemData[SameDate_Flag]
VAR TotalSameDateFlags = CALCULATE(SUM(OrderItemData[SameDate_Flag]), OrderItemData[LINE] = CurrentLine)
RETURN
IF(TotalSameDateFlags = TotalLineDetails, "F",
IF(TotalSameDateFlags > 0 && TotalSameDateFlags < TotalLineDetails, "P",
IF(TotalSameDateFlags = 0, "N", BLANK())))
The problem is that is not returning any P at all which is not possible.
the below is an example:
| DEALERORDER# | LINE | LINEDETAIL | AllocationStatus | FormatFirstReservationDate | FormatOrderCreateDate |
| 0000406226 | 1 | 1 | F | 1/19/2024 17:48 | 1/19/2024 17:48 |
| 0000406226 | 2 | 1 | F | 1/19/2024 17:48 | 1/19/2024 17:48 |
| 0000406226 | 2 | 2 | F | 1/19/2024 17:48 | 1/19/2024 17:48 |
| 0000406226 | 3 | 2 | N | 2/5/2024 9:53 | 1/19/2024 17:48 |
| 0000406226 | 3 | 1 | N | 1/27/2024 10:15 | 1/19/2024 17:48 |
| 0000406226 | 4 | 1 | F | 1/19/2024 17:48 | 1/19/2024 17:48 |
| 0000406226 | 5 | 1 | F | 1/19/2024 17:48 | 1/19/2024 17:48 |
| 0000406226 | 6 | 1 | F | 1/19/2024 17:48 | 1/19/2024 17:48 |
| 0000406226 | 7 | 1 | N | 1/24/2024 9:26 | 1/19/2024 17:48 |
| 0000406226 | 8 | 2 | N | 2/5/2024 10:22 | 1/19/2024 17:48 |
| 0000406226 | 8 | 1 | F | 1/19/2024 17:48 | 1/19/2024 17:48 |
| 0000406226 | 8 | 3 | N | 2/1/2024 18:46 | 1/19/2024 17:48 |
| 0000406226 | 9 | 1 | N | 1/24/2024 9:44 | 1/19/2024 17:48 |
| 0000406226 | 10 | 1 | N | 1/24/2024 9:34 | 1/19/2024 17:48 |
| 0000406226 | 11 | 1 | F | 1/19/2024 17:48 | 1/19/2024 17:48 |
| 0000406226 | 12 | 1 | N | 1/24/2024 9:34 | 1/19/2024 17:48 |
now, based on the formula, the entire line 8 should be marked as P, while it has one F and tow N. it looks like the formula is going row by row per detail but then is not applying correctly the status at line level.
How can i get this to work correctly?
thank you
4 Replies
- AmiraBedh
Super User
The logic you've implemented attempts to compare the count of SameDate_Flag across all details of a line with the total row count for that line. However, since the calculation is done row by row, it doesn't aggregate the way you expect it to for the purpose of assigning statuses across the entire line.
what do you need to achieve ?
- FaraclaFrequent Visitor
Hi Amira
what i m trying to achieve is to assing a correct status based on customer criteria.
in the example provided, the 3 lines of line 8 shoudl all be marked as P since only one of the line detail has
formafirstreservationdate = to ordercreatedate while the other 2 line details don t.
basically for each line, we check if the linedetail/s associated have formafirstreservationdate = to formatordercreatedate.- AmiraBedh
Super User
Please share some input and expected output.
- FaraclaFrequent Visitor
hi amira, below expected output based on 2 orders:
DEALERORDER# LINE LINEDETAIL AllocationStatus EXPECTED OUTPUT TotalOrderquantity AllocatedQuantity FormatFirstReservationDate FormatOrderCreateDate SameDate_Flag 405980 1 1 N N 10 1 1/22/2024 19:25 1/17/2024 12:25 0 405980 1 2 N N 1 1 1/22/2024 19:25 1/17/2024 12:25 0 405980 1 3 N N 1 1 1/22/2024 19:25 1/17/2024 12:25 0 405980 1 4 N N 1 1 1/22/2024 19:25 1/17/2024 12:25 0 405980 1 5 N N 1 1 1/22/2024 19:25 1/17/2024 12:25 0 405980 1 6 N N 1 1 1/22/2024 19:25 1/17/2024 12:25 0 405980 1 7 N N 1 1 1/22/2024 19:25 1/17/2024 12:25 0 405980 1 8 N N 1 1 1/22/2024 19:25 1/17/2024 12:25 0 405980 1 9 N N 1 1 1/22/2024 19:25 1/17/2024 12:25 0 405980 1 10 N N 1 1 1/22/2024 19:25 1/17/2024 12:25 0 405980 2 1 F F 10 10 1/17/2024 12:25 1/17/2024 12:25 1 405980 3 1 F F 10 10 1/17/2024 12:25 1/17/2024 12:25 1 405980 4 1 F F 3 3 1/17/2024 12:25 1/17/2024 12:25 1 405980 5 1 F F 3 1 1/17/2024 12:25 1/17/2024 12:25 1 405980 5 2 F F 1 1 1/17/2024 12:25 1/17/2024 12:25 1 405980 5 3 F F 1 1 1/17/2024 12:25 1/17/2024 12:25 1 405980 6 1 F F 3 1 1/17/2024 12:25 1/17/2024 12:25 1 405980 6 2 F F 1 1 1/17/2024 12:25 1/17/2024 12:25 1 405980 6 3 F F 1 1 1/17/2024 12:25 1/17/2024 12:25 1 406226 1 1 F F 2 2 1/19/2024 17:48 1/19/2024 17:48 1 406226 2 1 F F 2 1 1/19/2024 17:48 1/19/2024 17:48 1 406226 2 2 F F 1 1 1/19/2024 17:48 1/19/2024 17:48 1 406226 3 1 N N 2 1 1/27/2024 10:15 1/19/2024 17:48 0 406226 3 2 N N 1 1 2/5/2024 9:53 1/19/2024 17:48 0 406226 4 1 F F 1 1 1/19/2024 17:48 1/19/2024 17:48 1 406226 5 1 F F 5 5 1/19/2024 17:48 1/19/2024 17:48 1 406226 6 1 F F 1 1 1/19/2024 17:48 1/19/2024 17:48 1 406226 7 1 N N 1 1 1/24/2024 9:26 1/19/2024 17:48 0 406226 8 1 F P 9 9 1/19/2024 17:48 1/19/2024 17:48 1 406226 8 2 N P 13 5 2/5/2024 10:22 1/19/2024 17:48 0 406226 8 3 N P 8 8 2/1/2024 18:46 1/19/2024 17:48 0 406226 9 1 N N 1 1 1/24/2024 9:44 1/19/2024 17:48 0 406226 10 1 N N 3 3 1/24/2024 9:34 1/19/2024 17:48 0 406226 11 1 F F 1 1 1/19/2024 17:48 1/19/2024 17:48 1 406226 12 1 N N 1 1 1/24/2024 9:34 1/19/2024 17:48 0