Re: Measure should filter based on only one filed ...

Anonymous · ‎10-11-2018

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.

v-juanli-msft · ‎10-18-2018

Hi @Anonymous

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

Anonymous · ‎12-10-2018

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