cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nachiketjoshi
Frequent Visitor

Measure should filter based on only one filed and not all

I am calculating a simple measure  - Support Tickets per technician per day.

My Main data is in the table named - SupportRequests which has fields - RequestID, CreationDate,Technician,TimeTaken

I have another date table which has a column (TypeOfDay) indicating weather its a Sunday / Holiday or working day. I will take only working days in my calculation.

 

First I Created a measure : Total Days = CALCULATE(DISTINCTCOUNT(DateTable[Date]),DateTable[TypeOfDay] = "Working Day")

Then I Calculated: Daily Tickets = DISTINCTCOUNT('SupportRequests'[RequestID])/[Total Days]

Then I Calculated Daily Tickets per Technician : [DailyTickits]/DISTINCTCOUNT('SupportRequests'[Technician])

 

Now when I want to slice my data in my SupportRequests based on date. E.g. if I select 1st Sept to 7th September, Total Days should be 7. With the current measures, this is achieved. but then when I select a technician, it will further filter the date and will take only the dates on which the technician had a requests assigned to him. E.g. If a Technician John got a Ticket on 1st, 4th and 6th, his total days will be shown as 3 which is wrong as he was working on all 7 days.

 

Is there a way to achive this? I dont want any other field to filter the measure except CreationDate. 

 

 

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @nachiketjoshi

Make a test with the following data

DateTable

8.png

SupportRequests table

9.png

 

A question:

If a Technician  got a Ticket on 2nd, 6th and 9th, what should his total days be?

It is not clear how to calculate the total days.

 

Best Regards

maggie

 

Hi, Thanks for the reply

 

To answer your question, if a technician got ticket on 2nd ,6th and 9th, his total days will be 2nd  to 9th i.e. 8 Days. Just because he didn't get a ticket doesn't mean he was not working.

 

This is my problem. When i filter a technician, the report picks up only 2nd, 6th and 9th.  I want all 8 days in the calculations

SivaMani
Memorable Member
Memorable Member

Hi @nachiketjoshi,

 

Could please share sample data with your result(Expecting) to help you better?

 

Thanks,

Siva


Best Regards,
Siva Mani

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors