cancel
Showing results for
Did you mean:
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
Community Support

Make a test with the following data

DateTable

SupportRequests table

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

Frequent Visitor

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

Memorable Member

Thanks,

Siva

Best Regards,
Siva Mani

Announcements