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. Us...
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

     

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

Floriankx's avatar
Floriankx
Icon for Solution Sage rankSolution Sage
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

 

  • pnvinod's avatar
    pnvinod
    Icon for Helper I rankHelper I
    8 years ago

    We can also use the below custom column to return 0 for duplicate records

     

    RevisedHrs = if([Appearnces]=2&&[Approval status]="approved",0,[Booked hours])

     

    Hope it helps

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Thank you for that cool calculated column! Tht works fine for me. I created the "Appearances" and take this column as a filter to 1 for all of the pages.