Okay this sounds really simple but it has me stumped. I guess I basically want to show a count of unique references which show over/under a certain frequency (so I can later filter this). Here based calls transferred to a survey.
Working so far:
Where I get stuck:
Solved! Go to Solution.
Thanks for the rsponse, you have been a huge help and definitely shown me some things about DAX I was unaware of!
I eventually got aroud the issues by using a calculated column which gave a distinct count if the employee ID which then used against the limitnumber. This way I could easily filter those over 3 responses as required.
Seems there are lots of ways to di these things?
I'll also test this suggestion as it may prove useful.
Looking for something like that?
COUNTROWS( FILTER( DISTINCT(Responses[callID]) , [Number of responses] > 3 ) )
Note that if you filter on a specific question, number of response will most likely always be equal to 1, and you will not get any results. In that case, it might simply be better to just add a calculated column that states that the response belongs to a call that does includes more than 3 responses, and then filter on that column.
It will be help if you share some sample data for us.
Thanks for the responses, I have tried the Distinct and Filter functions but the measures I build seem to have too many filterrs for calculate to work. Here is a picture of the sample data.
So I need the number of Call_ID (Calls) which have 3 or more occurences in Call_ID. This would need to be a constant/static number which can then be displayed on various visuals (so I can see how many Calls have over 3 responses per Employee or Floor and breakdownin other ways e.g. Date).
You can write a measure with store the static amount of the limit, then compare the count of current id with the static value.
LimitNumber= 3 LargeThanLimit var currID=MAX(TABLE[Call_ID]) var TotalCount=COUNTX(FILTER(ALL(TABLE),[Call_ID]=currID),[Call_ID) return IF(TotalCount>[LimitNumber],TRUE(),FALSE())
Thnaks for this. I just need to know how to then change this into values. For example showing the number of CALLID that is over 3 for each Employee/Department etc?
>> Thnaks for this. I just need to know how to then change this into values.
Use the total count to replace the result "true":
LargeThanLimit var currID=MAX(TABLE[Call_ID]) var TotalCount=COUNTX(FILTER(ALL(TABLE),[Call_ID]=currID),[Call_ID) return IF(TotalCount>[LimitNumber],TotalCount ,FALSE())
I did this however it only returns the number of calls per call rather than the total number of calls per employee (or other level of filter/aggregation) and the return is a text return still (true/false but represented as a text count). I simply need to total the number o 'true' returns per aggregation (say employee or other) but I can't seem to count true values?
If you want to enable the filter affect, you can use ALLSELECTED function to instead the ALL function, sample formula:
LargeThanLimit var currID=MAX(TABLE[Call_ID]) var TotalCount=COUNTX(FILTER(ALLSELECTED(TABLE),[Call_ID]=currID),[Call_ID) return IF(TotalCount>[LimitNumber],TotalCount ,FALSE())
Thank you again for your time. However, I am still not getting the result I need (see picture).
There are 103 calls in total taken by employee 13 and so the number of those calls where there was over 3 responses would be a lower number (I expect to see about 90 maybe).
The steps needed are:
Filter by employee 13:
Distinct count of call ID (103)
Filter only only those callIDs which appear over 3 times
Disctint Count of those over 3 times = (90 maybe?)
The formula needs to select all the distinct calls which have appeared over 3 times then distinctly count them which I find tricky in DAX.