The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi guys,
I made a measure to use as a Filter on top of a visible to view only the Employees that are in service based on a specific Entry Date.
So Entry Date 10/1/2019 should only view Employees in service at that specific date.
File that includes all:
With the following measure works when EmployeeID is the unique record, when I set the measure as filter equal to 1 on the table visual. Unfortunately my EmployeeID isn't unique in the table: If Employees' names change, they get a new record with a new BeginDate and EndDate. Somehow this creates problems to be able to view the correct data. Down below I have 'Employee6' that has 3 records, and the measure below does not count a '1' for the third record of 'Employee6'.
PeriodFilter =
var _Begin = min(Employees[BeginDate])
var _End = min(Employees[EndDate])
return
if(
( _Begin <= min('Calendar'[Date]) && _Begin <= max('Calendar'[Date]))
&& ( _End >= min('Calendar'[Date]) && _End >= max('Calendar'[Date]))
,1,0
)
Sample Data:
| Employee | BeginDate | EndDate |
|------------|------------|------------|
| Employee1 | 1/30/2020 | 1/3/2021 |
| Employee2 | 9/20/2021 | 1/1/2022 |
| Employee3 | 1/3/2021 | 5/29/2021 |
| Employee4 | 7/5/2021 | 9/20/2021 |
| Employee5 | 11/10/2020 | 7/5/2021 |
| Employee6 | 1/30/2020 | 1/3/2021 |
| Employee6 | 1/4/2021 | 8/1/2021 |
| Employee6 | 8/2/2021 | 12/31/2099 |
| Employee7 | 7/5/2021 | 9/20/2021 |
| Employee8 | 1/5/2015 | 7/5/2021 |
| Employee9 | 9/20/2021 | 12/31/2099 |
| Employee10 | 1/1/2021 | 12/31/2099 |
| Employee11 | 9/8/2017 | 1/3/2021 |
| Employee12 | 1/3/2021 | 9/20/2021 |
| Employee13 | 7/5/2021 | 12/31/2099 |
| Employee14 | 1/5/2015 | 11/10/2020 |
| Employee15 | 1/30/2020 | 9/20/2021 |
| Employee16 | 1/1/2021 | 12/31/2099 |
| Employee17 | 1/5/2015 | 1/3/2021 |
| Employee18 | 9/20/2021 | 12/31/2099 |
| Employee19 | 1/3/2021 | 12/31/2099 |
| Employee20 | 11/10/2020 | 12/31/2099 |
Somehow it does show the correct result when I also drag fields 'BeginDate' and 'EndDate' in the Table. But I do not want to include those fields in the Table. Because it does work with those fields, I started to think I might need a SUMX function instead, that evaluates every record in the table, and gives me the '1' back for the 3rd record of Employee6, by using the following measure:
PeriodFilter_SUMX =
var _Begin = min(Employees[BeginDate])
var _End = min(Employees[EndDate])
return
SUMX(
Employees,
if(
( _Begin <= min('Calendar'[Date]) && _Begin <= max('Calendar'[Date])) && ( _End >= min('Calendar'[Date]) && _End >= max('Calendar'[Date]))
,1,0
)
)
This also doesn't work, apart that it gives a higher total number when the Date fields are included in the table.
How can I circumvent this problem within a measure? Any suggestions?
Solved! Go to Solution.
Please try this measure expression instead.
PeriodFilterNew =
VAR MinDate =
MIN ( 'Calendar'[Date] )
VAR MaxDate =
MAX ( 'Calendar'[Date] )
VAR Result =
COUNTROWS (
FILTER (
Employees,
Employees[BeginDate] <= MaxDate
&& Employees[EndDate] >= MinDate
)
)
RETURN
IF ( Result > 0, 1, 0 )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Works perfect, thanks! Though I don't really understand why this works, and my code does not.
Please try this measure expression instead.
PeriodFilterNew =
VAR MinDate =
MIN ( 'Calendar'[Date] )
VAR MaxDate =
MAX ( 'Calendar'[Date] )
VAR Result =
COUNTROWS (
FILTER (
Employees,
Employees[BeginDate] <= MaxDate
&& Employees[EndDate] >= MinDate
)
)
RETURN
IF ( Result > 0, 1, 0 )
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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 |
---|---|
136 | |
106 | |
102 | |
73 | |
59 |
User | Count |
---|---|
266 | |
127 | |
119 | |
100 | |
86 |