- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: DAX Count number of unique values in one colum...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

frankkinsey

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-10-2017
06:14 AM

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?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

frankkinsey

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-20-2017
04:10 AM

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

LaurentCouartou

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-10-2017
07:58 AM

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.

v-shex-msft

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-12-2017
10:54 PM

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: | |

If this post

For learning resources/Release notes, please visit: | |

frankkinsey

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-13-2017
03:42 AM

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

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-14-2017
12:34 AM

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: | |

If this post

For learning resources/Release notes, please visit: | |

Highlighted
##

frankkinsey

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-14-2017
03:53 AM

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

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-14-2017
04:43 PM

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: | |

If this post

For learning resources/Release notes, please visit: | |

frankkinsey

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-15-2017
03:28 AM

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

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-15-2017
11:47 PM

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

If this post

For learning resources/Release notes, please visit: | |

frankkinsey

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-16-2017
02:56 AM

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.