Forum Discussion

eyewee's avatar
eyewee
Frequent Visitor
2 years ago
Solved

Calculate Difference between 2 rows based on multiple column filters BUT handle the duplicate values

I have the following table called filtered_POR050:

Here is the sample: link 

I would want to find the difference between hours (HEURE LIV) for each ID TOURNEE, filtered by date(JOUR LIV) and postal code (GroupeCP). If the previous value is absent, then the result should be 0.
Currently this is how the final result may look like.


 

The following solution:

DETLA = 
VAR _earlier = CALCULATE(MAX(filtered_POR050[Label_HEURE LIV]), FILTER(filtered_POR050, filtered_POR050[ID TOURNEE] = EARLIER(filtered_POR050[ID TOURNEE]) && filtered_POR050[JOUR LIV] = EARLIER(filtered_POR050[JOUR LIV]) && filtered_POR050[CP] = EARLIER(filtered_POR050[CP]) && filtered_POR050[Label_HEURE LIV] < EARLIER(filtered_POR050[Label_HEURE LIV])))
RETURN
IF(_earlier <> BLANK(), filtered_POR050[Label_HEURE LIV] - _earlier, 0)


only works partially as it gives wrong return values when dealing with duplicate lines. As shown here:

The difference between these lines should be 0 instead of what is seen.

Any ideas?

Data Protection
Need Help
Tutorial Requests
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    eyewee 

    one way is to remove duplicates in PQ

     

     

    the other way is to create an index column in pq

     

     

    then modify the column

    DETLA = 
VAR _earlier = CALCULATE(MAX(Sheet1[Index]), FILTER(Sheet1, Sheet1[ID TOURNEE] = EARLIER(Sheet1[ID TOURNEE]) && Sheet1[JOUR LIV] = EARLIER(Sheet1[JOUR LIV]) && Sheet1[CP] = EARLIER(Sheet1[CP]) && Sheet1[Label_HEURE LIV] <= EARLIER(Sheet1[Label_HEURE LIV])&&'Sheet1'[Index]<EARLIER('Sheet1'[Index])))
VAR _earlier2=maxx(FILTER(Sheet1,'Sheet1'[Index]=_earlier),Sheet1[Label_HEURE LIV])
RETURN
IF(_earlier2 <> BLANK(), Sheet1[Label_HEURE LIV] - _earlier2, 0)

     

     

    pls see the attachment below

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi eyewee ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Measure =
VAR _1 =
    CALCULATE (
        MAX ( Sheet1[Index] ),
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[JOUR LIV] = SELECTEDVALUE ( Sheet1[JOUR LIV] )
                && Sheet1[CP] = SELECTEDVALUE ( Sheet1[CP] )
                && Sheet1[Index] < SELECTEDVALUE ( Sheet1[Index] )
        )
    )
VAR _2 =
    CALCULATE (
        MAX ( Sheet1[HEURE LIV] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Index] = _1 )
    )
VAR _3 =
    RANKX (
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[JOUR LIV] = SELECTEDVALUE ( Sheet1[JOUR LIV] )
                && Sheet1[CP] = SELECTEDVALUE ( Sheet1[CP] )
        ),
        CALCULATE ( MAX ( Sheet1[Index] ) ),
        ,
        ASC,
        DENSE
    )
VAR _4 =
    MAX ( Sheet1[HEURE LIV] ) - _2
RETURN
    IF ( _3 = 1, TIME ( 0, 0, 0 ), _4 )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

     

     

     

     

     

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    2 years ago

    eyewee 

    one way is to remove duplicates in PQ

     

     

    the other way is to create an index column in pq

     

     

    then modify the column

    DETLA = 
VAR _earlier = CALCULATE(MAX(Sheet1[Index]), FILTER(Sheet1, Sheet1[ID TOURNEE] = EARLIER(Sheet1[ID TOURNEE]) && Sheet1[JOUR LIV] = EARLIER(Sheet1[JOUR LIV]) && Sheet1[CP] = EARLIER(Sheet1[CP]) && Sheet1[Label_HEURE LIV] <= EARLIER(Sheet1[Label_HEURE LIV])&&'Sheet1'[Index]<EARLIER('Sheet1'[Index])))
VAR _earlier2=maxx(FILTER(Sheet1,'Sheet1'[Index]=_earlier),Sheet1[Label_HEURE LIV])
RETURN
IF(_earlier2 <> BLANK(), Sheet1[Label_HEURE LIV] - _earlier2, 0)

     

     

    pls see the attachment below

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi eyewee ,

    I have created a simple sample, please refer to my pbix file to see if it helps you.

    Measure =
VAR _1 =
    CALCULATE (
        MAX ( Sheet1[Index] ),
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[JOUR LIV] = SELECTEDVALUE ( Sheet1[JOUR LIV] )
                && Sheet1[CP] = SELECTEDVALUE ( Sheet1[CP] )
                && Sheet1[Index] < SELECTEDVALUE ( Sheet1[Index] )
        )
    )
VAR _2 =
    CALCULATE (
        MAX ( Sheet1[HEURE LIV] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[Index] = _1 )
    )
VAR _3 =
    RANKX (
        FILTER (
            ALL ( Sheet1 ),
            Sheet1[JOUR LIV] = SELECTEDVALUE ( Sheet1[JOUR LIV] )
                && Sheet1[CP] = SELECTEDVALUE ( Sheet1[CP] )
        ),
        CALCULATE ( MAX ( Sheet1[Index] ) ),
        ,
        ASC,
        DENSE
    )
VAR _4 =
    MAX ( Sheet1[HEURE LIV] ) - _2
RETURN
    IF ( _3 = 1, TIME ( 0, 0, 0 ), _4 )

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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