Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi, first of all sorry for my english I will try my best.
Our system have bug it's sometime duplicates almost the same information two or three times. It's look something like that:
ID created_at customer_id Start_date End_date work_time
1 2021.01.01 10:48 13 2021.01.01 2021.01.21 21
2 2021.01.01 10:48 13 2021.01.01 2021.01.21 21
3 2021.01.01 10:50 13 2021.01.01 2021.01.21 21
4 2021.01.05 11:12 5 2021.01.05 2021.01.08 4
5 2021.01.05 11:13 5 2021.01.05 2021.01.08 4
For work_time i create new collumn with =datediff(Start_date,End_date,day)+1
The task is calculate the average work time, but I can't use Average function becouse math don't work
(21+4)/2=12,5 (21+21+21+4+4)/5 = 14,2
maybe you have any ideas how remove dublicated rows or maybe here is other solution for my task.
And again sorry for my english)
Solved! Go to Solution.
@AndrejZevzikov , Create a measure like
averageX(summarize(Table, Table[customer_id], Table[created_at], Table[work_time]),[work_time])
Or you can delete duplicates in power query
https://www.youtube.com/watch?v=Hc5bIXkpGVE
Your best option is to delete duplicate rows in power Query as @amitchandak rightly suggests.
If you cannot access Power Query, you can create a new table as your "work" table (and ignore the original completely) using DAX. In the ribbon under Modeling, select "New Table" and type the equivalent DAX for your table (do not include the "ID" column since it's unique and will therefore just create the same table you already have) :
New Table =
SUMMARIZE (
'Old Table',
'Old Table'[created_at],
'Old Table'[customer_id],
'Old Table'[Start_date],
'Old Table'[End_date],
'Old Table'[work_time]
)
and you will get this:
Beware that you have two rows (highlighted in the image) which are the same except for the time they were created. If you know these are duplicate, you will have to define a business logic to identify them and then delete them.
To add a new "ID" column, choose new column in the ribbon and type:
ID =
RANK.EQ ( 'New Table'[created_at], 'New Table'[created_at], ASC )
Now you have a clean table to work with, and you can ignore the original.
Proud to be a Super User!
Paul on Linkedin.
@AndrejZevzikov , Create a measure like
averageX(summarize(Table, Table[customer_id], Table[created_at], Table[work_time]),[work_time])
Or you can delete duplicates in power query
Nice, it's seems working perfectly.
Thanks!
User | Count |
---|---|
121 | |
72 | |
71 | |
57 | |
50 |
User | Count |
---|---|
167 | |
83 | |
68 | |
65 | |
55 |