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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
bgepps
Frequent Visitor

Removing status' from average time calc

In my data set, i have the following columns of data that are being pulled in via a database.

 

Job Status

Status start date

Status end date

 

I have been able to calculate the average time for each job by doing using a datediff formula; Day Diff = DATEDIFF (job_status_history[status_start_date], job_status_history[status_end_date],DAY), and using the average measures in the reporting elements.

 

However, there are some job status that i would like to exclude from this calculation. Does anyone know how i would add a measure/formula to my table to keep the day diff calculation, but exclude certain status in the 'status' column? For example, 'client approved'.

5 REPLIES 5
tringuyenminh92
Memorable Member
Memorable Member

Hi @bgepps,

 

Choose Edit Query, right click in Job Status column, you will have some filter options and please try with it. if it's working for your case we could try another approach with DAX 

Hi @tringuyenminh92 - would this remove those status' from the other reports i have in my deck, though?

 

Or would i have to copy the table, and then filter out the status in the second table, with the same time difference formula's mentioned in my original post?

 

Appreciate your help,

 

Ben

Hi @bgepps,

Yes, this is filtering dataset, reports using this source (this table) will be filtered these data.

Hi @tringuyenminh92 - so if i follow my suggestion above, it should give me the information i require, and continue updating as the SQL database is populated with data?

 

If so i will try copying to a new table later and will keep you updated.

 

Thanks,

 

Ben

Hi @bgepps,

Just try and let us know if it's not working as your expectation.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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