Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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
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.
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
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
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |