topic Re: DAX to return the list of records in DAX Commands and Tips

DAX to return the list of records

DivakarKrishna — Tue, 11 Jan 2022 14:17:54 GMT

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.

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])

)

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

Re: DAX to return the list of records

ValtteriN — Tue, 11 Jan 2022 14:31:31 GMT

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!

Re: DAX to return the list of records

smpa01 — Tue, 11 Jan 2022 14:33:58 GMT

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

Re: DAX to return the list of records

DivakarKrishna — Tue, 11 Jan 2022 14:39:34 GMT

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.

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])

)

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])

)

Re: DAX to return the list of records

DivakarKrishna — Tue, 11 Jan 2022 14:43:11 GMT

Hi ValtteriN ,

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

Re: DAX to return the list of records

ValtteriN — Tue, 11 Jan 2022 14:47:01 GMT

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: