Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
For background - the hire date field has no relationship with the date table.
I need to count employees that were hired in the past year based on the date selected - but I need to remove the filter from the table so I can get all the employees.
Here is what I am trying - but the filter is still in place
------------------------------------------
Solved! Go to Solution.
Perhaps the FILTER is being called before the REMOVEFILTERS is applied. Try
TTM Hires =
VAR selecteddate =
MAX ( 'Date'[Date] )
VAR enddate =
DATEADD ( LASTDATE ( 'Date'[Date] ), -1, YEAR )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Monthly Workforce'[FDS ID] ),
REMOVEFILTERS ( 'Date' ),
'Monthly Workforce'[Hire Date] <= selecteddate
&& 'Monthly Workforce'[Hire Date] > enddate
)
Try changing the REMOVEFILTERS('Date'[Date]) to REMOVEFILTERS('Date'), so that filters on any column in the date table are removed. Its common with date tables for other filter columns to be added behind the scenes, e.g. a month sort order column.
This really has me puzzled. I am quite the beginner when it comes to DAX - but I thought what I was doing might work 😋
Thank you - but the change did not work.
I was able to get the correct answer by creating a calculated table that summarizes the Workforce table by month and then filtering for the needed timeframe. But I am really curious as to why I can't get the other formula to work.
Perhaps the FILTER is being called before the REMOVEFILTERS is applied. Try
TTM Hires =
VAR selecteddate =
MAX ( 'Date'[Date] )
VAR enddate =
DATEADD ( LASTDATE ( 'Date'[Date] ), -1, YEAR )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Monthly Workforce'[FDS ID] ),
REMOVEFILTERS ( 'Date' ),
'Monthly Workforce'[Hire Date] <= selecteddate
&& 'Monthly Workforce'[Hire Date] > enddate
)
Thank you so much. This is such a long journey. (Learning DAX)
If you can, take the SQL BI guys course on DAX, and definitely check them out on YouTube.
Enjoy DAX!
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |