DAX Count number of unique values in one column only if frequency is over a certain number

[frankkinsey]

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.

The Plan:

• To show the number of callers passed to a survey who failed/completed at least 4 questions (or answered at least 4 questions). This should then allow me to show a number of calls who completed/did not complete per employee (who transferred the call over).

The Data:

• Each row represents a single response.
• There is a CALL column giving each callID reference, these appear more than once (for calls where more than one response was left).
• There are other columns representing, employee ID/department etc (which eventually I’d like to filter to).

Working so far:

• Distinctcount on Calls show number of calls
• Countrows shows number of responses
• Calculate count of responses over 3. If I show the calls on a table (non-summarised) I can show which calls had over 3 responses in a Boolean expression (which I probably need to count up again?)

Where I get stuck:

• So I’m stacking a number of counts under/after each other which I can’t seem to do in DAX.
• I get stuck counting up the number of unique calls which have/fail to have at least 3 responses for each employee/department. I’m feeling I need to do some calculated table functions here?

[v-shex-msft]

Hi @frankkinsey,

It will be help if you share some sample data for us.

Regards,
Xiaoxin Sheng

[frankkinsey]

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).

[v-shex-msft]

Hi @frankkinsey,

You can write a measure with store the static amount of the limit, then compare the count of current id with the static value.

Measures:

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())

Regards,

Xiaoxin Sheng

[frankkinsey]

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?

[v-shex-msft]

Hi @frankkinsey,

>> 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())

Regards,

Xiaoxin Sheng

[frankkinsey]

Thank you,

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?

[v-shex-msft]

Hi @frankkinsey,

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())

Regards,

Xiaoxin Sheng

[frankkinsey]

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.

[v-shex-msft]

Hi @frankkinsey,

Sorry for slow response.

According to your description, you want to check the distinct call count of employees whose count number are large than limit, right?

I have a idea to deal with this requirement:

1. Use current employee id to filter records.
2. Calculate the distinct count which the column which choose from above table.
3. Use if function to deal with the result.

Measure: (located at employee table)

Current Count=
var currEmployeeID= max(Employee[EmployeeID])
var temp=FILTER(RecordTable, RecordTable[EmployeeID]=currEmployeeID)
var distinctCount= COUNTAX(DISTINCT(SELECTCOLUMNS(temp,[Call])),[Call])
Return
if(distinctCount>[limitNumber],distinctCount,false())

If above formula still not help, can you share me a pbix file? I will check it.

Regards,

Xiaoxin sheng

[frankkinsey · Accepted 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.

Thank again,

Frank

[LaurentCouartou]

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.