Forum Discussion

Faracla's avatar
Faracla
Frequent Visitor
2 years ago

"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#LINELINEDETAILAllocationStatusFormatFirstReservationDateFormatOrderCreateDate
000040622611F1/19/2024 17:481/19/2024 17:48
000040622621F1/19/2024 17:481/19/2024 17:48
000040622622F1/19/2024 17:481/19/2024 17:48
000040622632N2/5/2024 9:531/19/2024 17:48
000040622631N1/27/2024 10:151/19/2024 17:48
000040622641F1/19/2024 17:481/19/2024 17:48
000040622651F1/19/2024 17:481/19/2024 17:48
000040622661F1/19/2024 17:481/19/2024 17:48
000040622671N1/24/2024 9:261/19/2024 17:48
000040622682N2/5/2024 10:221/19/2024 17:48
000040622681F1/19/2024 17:481/19/2024 17:48
000040622683N2/1/2024 18:461/19/2024 17:48
000040622691N1/24/2024 9:441/19/2024 17:48
0000406226101N1/24/2024 9:341/19/2024 17:48
0000406226111F1/19/2024 17:481/19/2024 17:48
0000406226121N1/24/2024 9:341/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

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

  • Faracla's avatar
    Faracla
    Frequent 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.

  • Faracla's avatar
    Faracla
    Frequent Visitor

    hi amira, below expected output based on 2 orders:

    DEALERORDER#LINELINEDETAILAllocationStatusEXPECTED OUTPUTTotalOrderquantityAllocatedQuantityFormatFirstReservationDateFormatOrderCreateDateSameDate_Flag
    40598011NN1011/22/2024 19:251/17/2024 12:250
    40598012NN111/22/2024 19:251/17/2024 12:250
    40598013NN111/22/2024 19:251/17/2024 12:250
    40598014NN111/22/2024 19:251/17/2024 12:250
    40598015NN111/22/2024 19:251/17/2024 12:250
    40598016NN111/22/2024 19:251/17/2024 12:250
    40598017NN111/22/2024 19:251/17/2024 12:250
    40598018NN111/22/2024 19:251/17/2024 12:250
    40598019NN111/22/2024 19:251/17/2024 12:250
    405980110NN111/22/2024 19:251/17/2024 12:250
    40598021FF10101/17/2024 12:251/17/2024 12:251
    40598031FF10101/17/2024 12:251/17/2024 12:251
    40598041FF331/17/2024 12:251/17/2024 12:251
    40598051FF311/17/2024 12:251/17/2024 12:251
    40598052FF111/17/2024 12:251/17/2024 12:251
    40598053FF111/17/2024 12:251/17/2024 12:251
    40598061FF311/17/2024 12:251/17/2024 12:251
    40598062FF111/17/2024 12:251/17/2024 12:251
    40598063FF111/17/2024 12:251/17/2024 12:251
    40622611FF221/19/2024 17:481/19/2024 17:481
    40622621FF211/19/2024 17:481/19/2024 17:481
    40622622FF111/19/2024 17:481/19/2024 17:481
    40622631NN211/27/2024 10:151/19/2024 17:480
    40622632NN112/5/2024 9:531/19/2024 17:480
    40622641FF111/19/2024 17:481/19/2024 17:481
    40622651FF551/19/2024 17:481/19/2024 17:481
    40622661FF111/19/2024 17:481/19/2024 17:481
    40622671NN111/24/2024 9:261/19/2024 17:480
    40622681FP991/19/2024 17:481/19/2024 17:481
    40622682NP1352/5/2024 10:221/19/2024 17:480
    40622683NP882/1/2024 18:461/19/2024 17:480
    40622691NN111/24/2024 9:441/19/2024 17:480
    406226101NN331/24/2024 9:341/19/2024 17:480
    406226111FF111/19/2024 17:481/19/2024 17:481
    406226121NN111/24/2024 9:341/19/2024 17:480