How to make row with DAX to grouping person

[Anonymous]

hi, I'm a little confused to make a column that determines what group he belongs to someone (Compliance and not Compliance)

for the data as in Excel

so the conditions to enter into groups which are as follows:
1. when a person fills a time sheet for 1 day then he enters the Compliance category (you can see from status)
2. when a person does not fill in a time sheet 1 day (even if it's only 1 day) then he automatically enters the not compliance category

for excel I've made an example that I want

note

1= they fill the timesheet on that day
0= empty or they not fill the timesheet on that day

and data in my PowerBI just like this
note

timesheet on excel is IsComplete row on power bi

false = 0
true= 1

*i give the real data in excel*

and i want to show in power bi like this
so the visual will show how many person on january not compliance and compliance by LeadName

thank you

Best Regards,

Faishal Abdillah

[amitchandak]

Create a new column like

if(
sumx(filter(table,table[name] = earlier(table[name]) && format(table[Date],"YYYY-MM")=earlier(format(table[Date],"YYYY-MM"))),table[time sheet])
< countx(filter(table,table[name] = earlier(table[name]) && format(table[Date],"YYYY-MM")=earlier(format(table[Date],"YYYY-MM"))),table[time sheet])
,"Not Compliance","Compliance")

you might have to change filters as per need.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

@amitchandak,

i got an error in here

[amitchandak]

@Anonymous 

Not sure added some screenshot or pasted some error, not visible to me

[Anonymous]

@amitchandak,

ah sorry

[amitchandak · Accepted solution]

Move earlier inside and try

if(
sumx(filter(table,table[name] = earlier(table[name]) && format(table[Date],"YYYY-MM")<=format(earlier(table[Date]),"YYYY-MM")),table[time sheet])
< countx(filter(table,table[name] = earlier(table[name]) && format(table[Date],"YYYY-MM")<=format(earlier(table[Date]),"YYYY-MM")),table[time sheet])
,"Not Compliance","Compliance")