The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
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 |
---|---|
99 | |
93 | |
81 | |
63 | |
56 |
User | Count |
---|---|
247 | |
122 | |
110 | |
77 | |
72 |