Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello, everyone.
I need to create a metric based on the following logic:
Nº Employees :=
calculate(DISTINCTCOUNT('Employee'[ID]),
filter('Employee table',
year('Employee table'[Entry Date]) <= 2021
&& ('Employee table'[Leave Date] = BLANK() || (year('Employee table'[Leave Date]) >=2021))))
However, when i filter the date on PowerBI, it only displays employees that have the entry date of the filtered year.
For instance, if i select 2021, i need to see all employees that have not left until 2021. Not solely those with the entry date at 2021 like it's currently happening.
Please, any help will be much appreciated!
Thanks.
Solved! Go to Solution.
Last thing I can think to try
NumEmp :=
VAR endOfPeriod =
MAX ( 'Dim Date'[Date] )
VAR startOfPeriod =
MIN ( 'Dim Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Fact Internal Employee'[NIF Employee] ),
ALL ( 'Dim Date' ),
'Fact Internal Employee'[Employment Date] <= endOfPeriod,
(
'Fact Internal Employee'[Resignation Date] >= startOfPeriod
|| ISBLANK ( 'Fact Internal Employee'[Resignation Date] )
)
)
try
Nº Employees :=
CALCULATE (
DISTINCTCOUNT ( 'Employee'[ID] ),
REMOVEFILTERS ( 'Employee table' ),
YEAR ( 'Employee table'[Entry Date] ) <= 2021
&& (
ISBLANK ( 'Employee table'[Leave Date] )
|| ( YEAR ( 'Employee table'[Leave Date] ) >= 2021 )
)
)
I appreciate your help.
However, 'REMOVEFILTERS' is not being recognized on AS.
2021 was more of an example. The year is supposed to be whatever is filtered.
Any work arounds?
You could try
Nº Employees :=
VAR MaxDate =
YEAR ( MAX ( 'Date'[Date] ) )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Employee'[ID] ),
ALL ( 'Employee table' ),
YEAR ( 'Employee table'[Entry Date] ) <= MaxDate
&& (
ISBLANK ( 'Employee table'[Leave Date] )
|| ( YEAR ( 'Employee table'[Leave Date] ) >= MaxDate )
)
)
I adptaded your formula a bit and got the right results for each year:
NumEmp:=
VAR endOfPeriod =MAX ( 'Dim Date'[Date])
VAR startOfPeriod = MIN( 'Dim Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT('Fact Internal Employee'[NIF Employee]),
FILTER (
ALL('Fact Internal Employee'),
( ('Fact Internal Employee'[Employment Date] <= endOfPeriod )
&& ('Fact Internal Employee'[Resignation Date] >= startOfPeriod || 'Fact Internal Employee'[Resignation Date] = BLANK()))
))
)
The problem is that when i try to create an object with it against any other field, it presents repeated values:
Don't know if it helps, but the relationship is between Employee[entry date] and Date[Date]
you're removing all the filters on the employee table. try
NumEmp :=
VAR endOfPeriod =
MAX ( 'Dim Date'[Date] )
VAR startOfPeriod =
MIN ( 'Dim Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Fact Internal Employee'[NIF Employee] ),
REMOVEFILTERS ( 'Dim Date' ),
'Fact Internal Employee'[Employment Date] <= endOfPeriod
&& (
'Fact Internal Employee'[Resignation Date] >= startOfPeriod
|| ISBLANK ( 'Fact Internal Employee'[Resignation Date] )
)
)
Unfortunaly it doesn't work due to REMOVEFILTERS not being recognized. When i try to replace it with ALL i get an error saying "The expression contains multiple columns..." 😕
Last thing I can think to try
NumEmp :=
VAR endOfPeriod =
MAX ( 'Dim Date'[Date] )
VAR startOfPeriod =
MIN ( 'Dim Date'[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Fact Internal Employee'[NIF Employee] ),
ALL ( 'Dim Date' ),
'Fact Internal Employee'[Employment Date] <= endOfPeriod,
(
'Fact Internal Employee'[Resignation Date] >= startOfPeriod
|| ISBLANK ( 'Fact Internal Employee'[Resignation Date] )
)
)
Worker perfectly!
Thank you so much for your help!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
38 | |
21 | |
18 | |
15 |
User | Count |
---|---|
124 | |
39 | |
28 | |
24 | |
23 |