Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

 

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
Anonymous
Not applicable

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?

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 ? 

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
Not applicable

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

 Hi @Anonymous,

 

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.

 

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
Not applicable

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.

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

 

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

 

- Quentin

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.