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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Auto-Filter Tables Based on Date in Column

I am still relatively new to Power BI, so please forgive me if this is a simple question; I have been looking for a solution for days and haven't been able to find an answer. This is also my first time posting, so please tell me if I am posting in the wrong place.

 

 

I have created a report in Power BI that combines two reports for employee Corrective Action and Leaves of Absence. Any corrective action must be extended by the number of days an employee is on a Leave. 

 

The goal of my report is to quickly visualize all employees who are on corrective action and have been on a Leave of Absence since the date that Corrective Action started. Below is a screenshot of my sample report currenly,

HRBIs832_0-1609995103055.png

 

Currently, I am using the "Team Member" slicer to look at each individual employee and the date slicer in order to filter leave of absence dates to only show those dates AFTER the employee was placed on Corrective Action OR if there is a date in the "Reopened Date" field, I am filtering the date slicer to after that date. The Leave of Absence report contains dates prior to the corrective action due to how the system that the report is pulled from works. 

 

What I would like to do is eliminate the need for the date slicer. I would like the report to automatically filter the Leaves of Absence to after the date the corrective action started. Ideally, I would also like it to check if there is a date in the "Reopened Date" column and, if so, filter to after that date instead. Below is a screenshot of my relationships: 

 

HRBIs832_1-1609995361959.png

Please let me know if there is additional information I should include, this is my first time posting on the forum. 

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

You can modify the paycode at underlined section and create a new measure.

3.png

I have modified the demo, please try it:demo.pbix

 

Best Regards,

Link

View solution in original post

22 REPLIES 22
v-xulin-mstf
Community Support
Community Support

Hi, @Anonymous 

I have no access to the link, please provide a public link.

I suggest you to use One Drive for Business.

Best Regards,

Link

Anonymous
Not applicable

Unfortunately my organization doesn't allow us to share from OneDrive outside of the business. I have made the below link public. 

 

https://drive.google.com/file/d/1X4lahVs4M03LKOX4Z3891C2sQaa5FxsX/view?usp=sharing

Hi, @Anonymous 

I'm not clear your expected outcome, please details.
For example, you want to get the count of days that Mosby, Ted leave of absence, and the date must between date started and date ended.
In addition, i'm not clear the reopened date mean, please details.

Best Regards,

Link

Anonymous
Not applicable

Basically, I would like the report to auto filter to only count the leaves of absence AFTER the start date of the Corrective Action OR after the Reopen date if there is one. 


The reopen date just shows the last time I extended the Corrective ACtion. I'm not sure any of my sample data actually has any, but for example if I have to extend Mosby, Ted's Corrective ACtion by 10 days, the Reopen date will show when I did that and the reopen reason would note something like "Extendind due to leave of absence - Original End: (original end date), New End (End date after extension).

 

This is so I know I have already looked at the leaves of absence up to that date for the individual and know not to double count the dates before this.

Hi @Anonymous 
Create a measure as:

Measure = var _table=SELECTCOLUMNS(CAs,"Employee ID",CAs[Employee ID],"Date started",IF(CAs[Reopened Date]=BLANK(),CAs[Date started],CAs[Reopened Date])) 
return
var _table2=FILTER(GENERATE(_table,LoAs),[Employee ID]=[ID:]&&[Date]>=[Date started]&&[Pay Code]="Leave of Absence")
return
COUNTAX(_table2,[Employee ID])

Here is the demo, please try it: Auto-Filter Tables Based on Date in Column

Best Regards,

Link

Anonymous
Not applicable

What should I do with the measure? In your example, the card visual that has the measure is returning blank.

Hi @Anonymous 

The measure return the count the leaves of absence after the start date of the corrective action or after the reopen date if there is one.

The card visual that has the measure is returning blank because there have no data that meets the conditions.

Please try the demo and check if it works.

Best Regards,

Link

Okay, I tried adding in some data that met the criteria on Mosby, Ted and Erickson, Marshall and the measure is still showing blank.

Hi @Anonymous 

I wonder if i get your expected outcome or not.
For example, Mosby, Ted leaves of absence after date started or reopen date if there is one.Then count the days of leave of absence.
If it has mistakes, please show me the right.
Could you share the sample data on format of excel?
Expected output would help tremendously.

Best Regards,

Link

Anonymous
Not applicable

I have updated the excel file at the below link to reflect my sample data. 

 

https://drive.google.com/file/d/1Ww39-2Ls18TwdFKBxKsrwZuocIyf-17V/view?usp=sharing

 

Expected output: 

 

Mosby, Ted recieved Corrective Action with a start date of 11/3/2020.

 

He has a total of 8 leaves on the following dates: 

Leave of Absence: 1/1/20, 1/2,20, 1/3/20, 1/6/20, and 1/7/20

Absence: 1/1/21, 1/2/21, 1/3/21

 

As you can see, only 3 of these dates fall after the start of his corrective action. So I would like it to filter out 1/1/20-1/7/20. So the ABS/LoA Days column (Or alternatively your measure) should show a count of 3 for Ted and the visual on the right-hand side will only show me those dates that fall after the Date Started (though really it's the count that's important, I can live with the visual on the right still showing me the additional days). 

 

I hope this makes sense... please let me know if you need further clarification.

Hi @Anonymous 

I modify the reopen date to 2019/11/25 and 2019/12/05.

Here is my output:

v-xulin-mstf_0-1610523628683.png

Maybe there are some  formatting errors in your excel table.

Here is the demo, please try it: demo.pbix

Best Regards,

Link

For your date format, are you using MM/DD/YYYY or DD/MM/YYYY?

I can see your measure is counting on the demo, but it's still not filtering out the dates after Ted recieved his corrective action on 11/3/2020 (MM/DD/YYYY). It is still showing dates from the months prior to November.

There @HRBIs832

When i drag the measure into the table, it works.

v-xulin-mstf_1-1610525251489.png

Best Regards,

Link

Anonymous
Not applicable

Okay, I got it working! Is there any way I can have it look for multiple pay codes or should I just do a different measure for each?  For example, currenlyt it looks for Pay Cod=Leave of Absence and counts those, is there a way to, for instance, count both the Leave of Absence pay code and others, such as Military Leave?

Hi @Anonymous ,

 

You can modify the paycode at underlined section and create a new measure.

3.png

I have modified the demo, please try it:demo.pbix

 

Best Regards,

Link

Anonymous
Not applicable

That's exactly what I was looking for! Thank you so much for all your help!

amitchandak
Super User
Super User

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Anonymous
Not applicable

I don't have either online anywhere, is there a way I can upload from a local file?

@Anonymous , You can upload to one drive or dropbox and share a link

Anonymous
Not applicable

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.