Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
The Data:
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.
Thank again,
Frank
Hi @frankkinsey,
It will be help if you share some sample data for us.
Regards,
Xiaoxin Sheng
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).
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
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?
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
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?
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
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.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
88 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |