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, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

DAX to count employees from base table according to transaction table date (Not Matching)

Hi

 

I have a forms table that is collecting data for each employee per day. I have a 1 to many relationship to employee base table.

I did a match using related table function to establish employees that responded vs not. 

 

MatchEmployee = LASTNONBLANK(SELECTCOLUMNS(RELATEDTABLE('Table1'),"Employee_No",[employee_no]),TRUE())
 
So would I need to now include date in DimEmployee so I have 2 relationships to forms table of date and employee ID? or is the dax that can do this without altering DimEmployee?
So I basically count employee responded for monday = 100
Total Base is = 500 .there 400 is ones that didnt respond. How would I get this in Dax?  Taking into account I dont have a date field in Dimemployee but would like the static base to compare to each day and not return total count that wont slice and dice according to the date
 
 
 
 
8 REPLIES 8
Greg_Deckler
Super User
Super User

Read this through a couple times. Hard to understand what is going on here. MatchEmployee, is that a column, a measure? In what table? Not sure I understand where date comes into this.

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi

Thanks for your reply. I have DimEmployee table linked to FormsEmployee Transaction table.

When I use the date slicer, it filters the FormsEmployee table because there is a date column but doesnt exist in DimEmployee.

 

Its 1 to many relationship, if I want a total base of employees I apply dax over the DimEmployee to get Total count.

I apply dax to FormsEmployee to get total number of employee responses (When completing MS Form).  

 

So TotalCount = 500, Employee Responses = 250. But I would like to know who where the employees that did not respond meaning difference of 250 employees that do not match to FormsEmployee table when filtering by  a date slicer. 

 

MatchEmployee(Calculated Column) = LASTNONBLANK(SELECTCOLUMNS(RELATEDTABLE('Table1'),"Employee_No",[employee_no]),TRUE())

 

DAX -Employees NOT Responded = IF(ISBLANK(COUNTBLANK(Query1[MatchEmployee]))=True,0,COUNTBLANK(Query1[MatchEmployee]))
 
Problem i have is when filtering each date the Employees NOT responded does not adjust accordingly.Static count.
 
 

 

 

 

 

Anonymous
Not applicable

Yeah, it is static because columns are ALWAYS static. You need a dynamic measure that will tell for each employee in the current context whether or not the employee responded. Then you can use this measure to filter your employees on a visual (table, matrix) for the ones you want to see.

Best
D
Anonymous
Not applicable

Create this measure:

[# Emps that Responded] =
DISTINCTCOUNT( FormsTable[EmployeeID] )

and put all the employees from dimEmployee in a table visual. Then in the filtering pane drop this measure in the Filter This Visual section and set the condition to "is greater than 0". This will show you all the ones that did respond.

If you want to see those that did not respond, set the filter to "is 0."

Best
D
Anonymous
Not applicable

Thanks. And if I select date slicer to a specific day, will this dynamically change (for the ones that did not respond)because the date coumn exists in FormsEmployee table and not the DimEmployee (Unless its a many to many scenario where I have to introduce a date in DimEmployee and use composite key?)

Anonymous
Not applicable

First, you can always check it yourself. Second, it'll work correct in any circumstances. Third, please learn about the correct design:

https://www.youtube.com/watch?v=78d6mwR8GtA

https://www.youtube.com/watch?v=_quTwyvDfG0

For instance, you should never, ever expose fields from a fact table to the end user. You should always have a dedicated Calendar in your model. And many, many other things... IF YOU WANT TO PRODUCE CORRECT NUMBERS AND SIMPLE FAST DAX.

If you don't follow the rules, be prepared for all kind of mayhem.

Best
D
Anonymous
Not applicable

Hi Thank Its all working as per your recommendation and thanks for the videos already watching!!

 

Anonymous
Not applicable

Hi

 

I tried this, but returns blank as a count 

Check =



IF (ISFILTERED(Table1[Completion time].[Date]) ,



  DISTINCTCOUNT(Query1[employee_no]))

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors