Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
LP280388
Resolver II
Resolver II

Is employee active within the given period

Hi Team,

 

I need a help in filtering a visual to show only the active users between the From and To Date.  Below is how my data looks like. I have slicer from StartDate and EndDate. 

When a user selects the date from these Slicers, the table visual should only show the employees who were active during this time. 

Im trying to achieve the last column marked in Red below. 
the criteria would be,
1. when a user has startdate and end date within the selected period

2. when a user has startdate or end date within the selected period

3. when a user has startdate before the startdateslicer and enddate is blank

 

In the above scenarios, the condition turns to true and I like all the users in the table visuals where the condition is true.

 

Kinldy help on achieving this please.

 

StartDate SlicerEndDate Slicer
01/01/202204/25/2022

 

IdStartDateEndDateActive?
1000104/14/201106/24/2011No
1000204/26/2022 No
1000304/04/2022 Yes
1000404/20/2022 Yes
1000504/24/2022 Yes
1000607/31/2011 Yes
1000711/10/201112/08/2011No
1000803/15/202203/15/2022Yes
1000911/24/202112/28/2021No
1001004/14/201104/26/2023Yes
1001103/15/2022 Yes
1001205/19/201205/19/2024Yes
1001304/14/2011 Yes
1001408/14/202012/18/2021No
1001503/15/2022 Yes
1 ACCEPTED SOLUTION
PC2790
Community Champion
Community Champion

See if the below measure works:

Measure = 
VAR FirstDay = CALCULATE(
        MIN('DateTable'[Date]),
        ALLSELECTED('DateTable'[Date])
    )
    VAR LastDay = CALCULATE(
    MAX('DateTable'[Date]),
    ALLSELECTED('DateTable'[Date])
)
Return 
SWITCH(TRUE(),
 FirstDay < MAX(MainTable[StartDate]) && Max(MainTable[EndDate])<LastDay,"Yes",
 FirstDay < MAX(MainTable[StartDate]) && Max(MainTable[EndDate]) = BLANK(),"Yes",
 "No")

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi,

 

Try this measure it shoul work

JamesFr06_0-1652358043934.png

 

PC2790
Community Champion
Community Champion

See if the below measure works:

Measure = 
VAR FirstDay = CALCULATE(
        MIN('DateTable'[Date]),
        ALLSELECTED('DateTable'[Date])
    )
    VAR LastDay = CALCULATE(
    MAX('DateTable'[Date]),
    ALLSELECTED('DateTable'[Date])
)
Return 
SWITCH(TRUE(),
 FirstDay < MAX(MainTable[StartDate]) && Max(MainTable[EndDate])<LastDay,"Yes",
 FirstDay < MAX(MainTable[StartDate]) && Max(MainTable[EndDate]) = BLANK(),"Yes",
 "No")

@PC2790 Thank you. with a little tweak I was able to get to the result withe approach you have provided. Thank you very much

 

LP280388
Resolver II
Resolver II

@amitchandak Hi Amit thank you very much for the link. 

Ratherthan count, Im looking for a column Indicator to show the active users for the selected period.


Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

  How can I modify this to  show as a column instead of measure? Please help.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.