DAX to return the list of records

[DivakarKrishna]

Dear Team,

Required help to write DAX for the below requirement.

I have a table with employee attendance data, I need to take count and employee list who met mandatory work hours and who doesn't meet as well.

I wrote the DAX for total count for both and it's working, but I need to list the employees along with data and duration, and I am not sure on how to achieve this.

1)

Employees with more hours =

CALCULATE(

DISTINCTCOUNT(WFC_Attendance_Data[User_Email]),

FILTER(VALUES(WFC_Attendance_Data[User_Email]), [Total Hours Worked]>='All Measures'[Expected Working Hours])

)

2)

Employees with less hours =

CALCULATE(

DISTINCTCOUNT(WFC_Attendance_Data[User_Email]),

FILTER(VALUES(WFC_Attendance_Data[User_Email]), [Total Hours Worked] < 'All Measures'[Expected Working Hours])

)

Could someone help please me on how to list out all employee's who met & who doesn't met our expected working hours ?

Thanks,

P.K.Divakar

[smpa01]

@DivakarKrishna  can you possibly provide a sample pbix/sample data?

[DivakarKrishna]

Hello,

Below is the sample count's that I am getting from my measure which is correct. Now, I want to list out those employees above working hours (60 employees) and employees below working hours (7 employees) in a matrix table in next page.

I have calculated below counts using below measures.

1)

Employees with more hours =

CALCULATE(

DISTINCTCOUNT(WFC_Attendance_Data[User_Email]),

FILTER(VALUES(WFC_Attendance_Data[User_Email]), [Total Hours Worked]>='All Measures'[Expected Working Hours])

)

2)

Employees with less hours =

CALCULATE(

DISTINCTCOUNT(WFC_Attendance_Data[User_Email]),

FILTER(VALUES(WFC_Attendance_Data[User_Email]), [Total Hours Worked] < 'All Measures'[Expected Working Hours])

)

[ValtteriN]

Hi,

You can create a filter measure and use that in your visual to get the list of names. E.g. IF([Total Hours Worked] >= [Expected Working Hours],1,0). 

Now use this in your visual (example with similar measure):

Here I get a list of Customers when year =2021.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[DivakarKrishna]

Hi @ValtteriN ,

Sorry, it doesn't work. It didn't return any records from table.

[ValtteriN · Accepted solution]

 @DivakarKrishna The idea behind using filter measure is to first create table with your name column. Then use the measure as a filter in the visual. In my example earlier you can notice that the filter measure (measure 5) is applied on a visual: