Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
DivakarKrishna
Frequent Visitor

DAX to return the list of records

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

1 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: 

ValtteriN_0-1641912409099.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
smpa01
Super User
Super User

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

DivakarKrishna_0-1641911815969.png

 

ValtteriN
Super User
Super User

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

ValtteriN_0-1641911467464.png


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!


 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @ValtteriN ,

 

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

 

DivakarKrishna_0-1641912175109.png

 

 @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: 

ValtteriN_0-1641912409099.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.