Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
So I have multiple people assigned to different 'Tasks'. I want only 1 person assigned to each task. I know the names of the people whose tasks I want to keep. So basically:
Task | Agent |
1 | Joe |
1 | Sally |
2 | Patricia |
3 | Joe |
3 | Debra |
4 | Joe |
In this example, I want to delete the duplicate tasks based on if Joe is the agent. If the task isn't a duplicate and Joe is assigned, I still want to keep it.
Solved! Go to Solution.
Hi @Anonymous
Try to add a new table with this code:
New Table =
FILTER (
'Table',
'Table'[Agent] = "Joe"
|| CALCULATE (
DISTINCTCOUNT ( 'Table'[Agent] ),
ALLEXCEPT ( 'Table', 'Table'[Task] )
) = 1
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
Hi @Anonymous ,
Not sure if I understand your correctly but the formula provided by @VahidDM is basically correct.
You might need to modify “= Joe” to “<> Joe".
Table 2 =
FILTER (
'Table',
'Table'[Agent] <> "Joe"
|| CALCULATE (
DISTINCTCOUNT ( 'Table'[Agent] ),
ALLEXCEPT ( 'Table', 'Table'[Task] )
) = 1
)
Best Regards,
Jay
Hi @Anonymous
Try to add a new table with this code:
New Table =
FILTER (
'Table',
'Table'[Agent] = "Joe"
|| CALCULATE (
DISTINCTCOUNT ( 'Table'[Agent] ),
ALLEXCEPT ( 'Table', 'Table'[Task] )
) = 1
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
@Anonymous
you can use DAX to create a new table
Table 2 =
VAR tbl1=ADDCOLUMNS('Table',"check",if('Table'[Task]=maxx(FILTER('Table','Table'[Task]=EARLIER('Table'[Task])&&'Table'[Agent]="Joe"),'Table'[Task]),"Yes"))
VAR tbl2=FILTER(tbl1,[check]="")
VAR tbl3=FILTER(tbl1,[check]="Yes"&&'Table'[Agent]<>"Joe")
return SELECTCOLUMNS(union(tbl2,tbl3),"Task",'Table'[Task],"Agent",'Table'[Agent])
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |