Forum Discussion

DylanVelo's avatar
DylanVelo
Regular Visitor
2 years ago
Solved

Row numbers with conditions

Hi everyone!

im trying to get the row number over partition with a measure with DAX, it need to be like that due the user needs to change the filter dinamicaly. 

this si my current measure:

RN new =
VAR datetime = MAX('Updated Calendar'[Date]) + SELECTEDVALUE('Time'[Time])

RETURN
CALCULATE(
ROWNUMBER(
        ALLSELECTED('All Reserves'[ID_DATE], 'All Reserves'[MASTID_EMPNO], 'All Reserves'[UPDATEDATE],'All Reserves'[UpdatedTime]),
        ORDERBY( 'All Reserves'[UPDATEDATE], DESC,'All Reserves'[UpdatedTime], DESC),
        DEFAULT,
        PARTITIONBY('All Reserves'[MASTID_EMPNO], 'All Reserves'[ID_DATE])
)

so we are looking for an historical information and the user wants to know th laste status by each partion of empno and id_date. The problem is that in this example the filter is set to 12/7/2023 10:30 AM and with an specific partion on of the records is after that datetime and its showing this:
MASTID_EMPNOID_PRGDATEdatetimeRN
4287112023120711/8/2023 13:312
4287112023120712/7/2023 11:301

as the datetime of the second row is after the filtered day, that one shoudlnt be include in the rownumber. the expected output should look something like this:
MASTID_EMPNOID_PRGDATEdatetimeRN
4287112023120711/8/2023 13:311
4287112023120712/7/2023 11:30 

this is the sample data you can use and the expected output:
MASTID_EMPNOID_PRGDATEdatetimeExpected RN
4141622023120711/8/2023 19:451
4141622023120711/8/2023 9:412
4236572023120711/8/2023 19:441
4236572023120711/8/2023 9:412
4269892023120711/8/2023 13:312
4269892023120712/6/2023 11:341
4272602023120711/8/2023 13:311
4273722023120711/29/2023 8:421
4273722023120711/8/2023 13:312
4274742023120711/8/2023 13:311
4275892023120711/8/2023 13:311
4282032023120711/8/2023 13:311
4287112023120711/8/2023 13:311
4287112023120712/7/2023 11:30 
4294942023120711/8/2023 15:283
4294942023120711/10/2023 11:372
4294942023120712/6/2023 12:001
4297982023120711/8/2023 13:312
4297982023120712/6/2023 11:441
4302202023120711/16/2023 8:041
4302202023120711/8/2023 13:312
4311502023120711/8/2023 13:311

The final idea is with another measure count just the ones with a 1 in the RN to selected the latest version at the selected datetime filter. If you have a better solution is welcome too.
Need Help
  • v-zhangti's avatar
    v-zhangti
    2 years ago

    Hi, DylanVelo 

     

    You can try the following methods.

    Measure = 
Var _N1=CALCULATE ( COUNT ( 'Table'[MASTID_EMPNO] ),
    FILTER ( ALL ( 'Table' ),
        [MASTID_EMPNO] = SELECTEDVALUE ( 'Table'[MASTID_EMPNO] )
            && [datetime] >= SELECTEDVALUE ( 'Table'[datetime] )
            && [datetime] <= SELECTEDVALUE ( Slicer[Slicer] )
    )
) + 0
Var _N2=CALCULATE ( COUNT ( 'Table'[MASTID_EMPNO] ),
    FILTER ( ALL ( 'Table' ),
        [MASTID_EMPNO] = SELECTEDVALUE ( 'Table'[MASTID_EMPNO] )
            && [datetime] >= SELECTEDVALUE ( 'Table'[datetime] )
    )
)
Return
IF(SELECTEDVALUE(Slicer[Slicer])=BLANK(),_N2,_N1)

    Is this the result you expect? Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

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

     

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    DylanVelo , seem like you need latest row, use this approch

    Last Qty = Var _max = maxx(filter( ALLSELECTED(Data1), Data1[MASTID_EMPNO] = max(Data1[MASTID_EMPNO]) && Data1[ID_PRGDATE] = max(ID_PRGDATE) ),Data1[datetime])
    return
    CALCULATE(Count(Data1[datetime]), filter( (Data1), Data1[MASTID_EMPNO] = max(Data1[MASTID_EMPNO]) && Data1[ID_PRGDATE] = max(ID_PRGDATE) && Data1[datetime] =_max))

     

     

    https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

    https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee884e

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    2 years ago

    Hi, DylanVelo 

     

    You can try the following methods.

    Measure = 
Var _N1=CALCULATE ( COUNT ( 'Table'[MASTID_EMPNO] ),
    FILTER ( ALL ( 'Table' ),
        [MASTID_EMPNO] = SELECTEDVALUE ( 'Table'[MASTID_EMPNO] )
            && [datetime] >= SELECTEDVALUE ( 'Table'[datetime] )
            && [datetime] <= SELECTEDVALUE ( Slicer[Slicer] )
    )
) + 0
Var _N2=CALCULATE ( COUNT ( 'Table'[MASTID_EMPNO] ),
    FILTER ( ALL ( 'Table' ),
        [MASTID_EMPNO] = SELECTEDVALUE ( 'Table'[MASTID_EMPNO] )
            && [datetime] >= SELECTEDVALUE ( 'Table'[datetime] )
    )
)
Return
IF(SELECTEDVALUE(Slicer[Slicer])=BLANK(),_N2,_N1)

    Is this the result you expect? Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

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