Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Measure to compare columns

Hello,

 

first of all to understand:

I have a dataset, where I can see which employee booked how many hours on which confirmation and on which date. I can see if they are approved hours or not. Usually they are not approved and later on they get approved. So the status should be different in the same row. Sometimes this isn´t happening and I have a doubled entry, one for approved and the same for not approved.

 

What I want is a measure, which should set the booked hours to zero, when every column (Work date, Confirmation number, Number of booked hours, Employee ID...) is equal, except the status approval status. This is the only column which should be not equal.

 

Do you have an idea guys?

 

 

Need Help
  • Floriankx's avatar
    Floriankx
    8 years ago

     

    Hello,

     

    This wouldn't be a smooth solution.

     

    quentin_vigne: Index is a great idea, but in this case, the two rows would get different index numbers which wasn't my intention.

     

    Maybe you could try the following:

    Create a calculated column:

     

    Appearnces=
VAR Employee=[Employee]
    VAR Date=[Work Date]
    VAR ConfID=[Confirmation number]
    ....
    RETURN
    COUNTROWS(FILTER(TableName,[Employee]=Employee&&[Work Date]=Date&&[Confirmation number]=ConfID))

    This measure should give you the number of appearances. Should be either 1 or 2.

     

    Then you can create a reverse filter:

    TotalNoDouble=CALCULATE(
    SUM([Booked Hours],
    FILTER(Table,NOT([Appearances]=2&&[Approval status]="not approved")))

    Give it a try and let us know.

     

    Best regards

     

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Here is an example:

     

    Employee                    Work date                 Confirmation number                Approval status                  Booked hours

    Thomas                          28.05.2018                    123456                                       not approved                      8

    Thomas                          28.05.2018                    123456                                       approved                            8

     

     

    -> Because our system doesn´t overwrite the first row sometimes, I have the problem that it seems like the employee Thomas booked 16 hours on that day instead of the correct 8. Can I set the booked hours of the last row to zero?

    • Floriankx's avatar
      Floriankx
      Icon for Solution Sage rankSolution Sage
      8 years ago

      Do you have any unique identifier, like confirmation number or similar?

       

      You could FILTER but then it filters wether there are two rows or not.

       

      With unique identifier you could check how many rows there are with this identifier and exclude the "not approved" one.

       

      Best reagards.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hello Floriankx,

         

        thanks for your answer! I don´t have a unique identifier. Maybe it is possible to copy all the columns and transform these to one column as a "unique identifier"?

    • quentin_vigne's avatar
      quentin_vigne
      Icon for Solution Sage rankSolution Sage
      8 years ago

      Hi Anonymous

       

      I don't know if you could do this :

       

      Create another column 

       

      BookedVerification = IF(Table1[Approval status]="approved";Table1[Booked hours];0)

       

      And use this new column to make every other calculation ?