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

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

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?

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
frankkinsey Frequent Visitor
Frequent Visitor

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

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

 

11 REPLIES 11

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

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.

Community Support Team
Community Support Team

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

Hi @frankkinsey,

 

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

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
frankkinsey Frequent Visitor
Frequent Visitor

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

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

 

Capture1.PNG

Community Support Team
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
frankkinsey Frequent Visitor
Frequent Visitor

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

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?

Community Support Team
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
frankkinsey Frequent Visitor
Frequent Visitor

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

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?

 

 

 

Community Support Team
Community Support Team

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

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Highlighted
frankkinsey Frequent Visitor
Frequent Visitor

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

Results.png

 

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.