Need help with DAX

[Maheshguptaz]

Hi all ,

I'm unable to get the logic to implement in DAX for one of the queries that I have. 

Scenario:

I've 3 columns namely "Report_Name", "User_List" and "User_status".

if a report is shared with 3 users and out of these 3, any 1 user is active from the User_List then I need the output as "Retain". If all the user's status is Inactive, I need the output as "Delete".

Below is a sample of the data:

Report_Name	User_List	User_status	Output
Report1	User1	Active	Retain
Report1	User2	Active	Retain
Report1	User3	Active	Retain
Report1	User4	Inactive	Retain
Report1	User5	Inactive	Retain
Report2	User6	Inactive	Delete
Report2	User5	Inactive	Delete
Report2	User7	Inactive	Delete
Report2	User8	Inactive	Delete

Any help with this is appreciated. Thanks in advance!!

Regards
Mahesh

[Daniel29195 · Accepted solution]

@Maheshguptaz 

output

Column = 
var r = Table2[Report_Name]

var f= 

FILTER(
    Table2,
    Table2[Report_Name] = r && Table2[User_status] = "Active"
)

return
IF(ISEMPTY(f),"Delete","Retain")

let me know if this works. 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠