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

[Anonymous]

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

[Greg_Deckler]

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

[Anonymous]

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]

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]

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]

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]

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]

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

[Anonymous]

Hi

I tried this, but returns blank as a count 

Check =

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

  DISTINCTCOUNT(Query1[employee_no]))