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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.