cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Floriankx Established Member
Established Member

Re: Measure to compare columns

 

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

 

View solution in original post

9 REPLIES 9

Re: Measure to compare columns

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 Established Member
Established Member

Re: Measure to compare columns

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.

quentin_vigne Senior Member
Senior Member

Re: Measure to compare columns

Hi @ChristopherKa

 

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 ? 

Re: Measure to compare columns

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 Senior Member
Senior Member

Re: Measure to compare columns

@ChristopherKa

 

If you need unique identifier, you can go to the data tab > query editor > add new column > Index column > starting from 1

 

- Quentin

Highlighted
Floriankx Established Member
Established Member

Re: Measure to compare columns

 

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

 

View solution in original post

Community Support Team
Community Support Team

Re: Measure to compare columns

 Hi @ChristopherKa,

 

Have you solved your problem with the help?

 

If you have solved, please note that always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If you still need help, please feel free to ask.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
pnvinod Regular Visitor
Regular Visitor

Re: Measure to compare columns

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

Re: Measure to compare columns

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.

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 291 members 2,812 guests
Please welcome our newest community members: