Forum Discussion
Countrows as a filter within a CALCULATE function
Hello
I'm facing a problem when filtering data inside a CALCULATE formula. What do I aim to do? To filter the people that appeared more than n times in my database (the highlighted part in my formula). However, whenever I try applying any count related filter I got the problem shown in the image below. How do I apply a count rows filter (or any function alike) to do so? Any thoughts?
Thanks in advance.
Best regards,
- Pedro H.
Not surprising there throws an error message. Filter 1,2,3 are based on row-level whereas filter 4 is an aggregation filter. Its SQL equivalent reads,
SELECT [assignee], COUNT(DISTINCT [assignee]) FROM BASE B JOIN Teste_Agents1 T ON B.[assignee]=T.[Nome] AND B.[Setor]=T.[Setor3] --row-level WHERE [assignee] IS NOT Null GROUP BY [assignee] HAVING COUNT([assignee])>7 --aggregation filterconversion of DAX measure,
= COUNTROWS( FILTER( CALCULATETABLE( VALUES( Base[assignee] ), filter1, filter2 ), CALCULATE( COUNTROWS( base ) > 7 ) && NOT ISBLANK( Base[assignee] ) ) )
5 Replies
Anonymous
You can make a variable in your measure that is the list of people with their count, then filter that list and use the filtered list in your calculate.
Count Rows Filtered = VAR _List = FILTER( ADDCOLUMNSS( DISTINCT(Base[Assignee]), "@Count",CALCULATE(COUNT(Base[ticket_number]))), [@Count] > 7 ) RETURN CALCULATE( DISTINCTCOUNTNOBLANKK(Base[ticket_number]),_List )The VAR _List has the list of people with a count > 7 then the _List is used as a filter in the CALCULATE
Yes, add the VAR that generates the list and use it in your CALCULATE. It is easier to work with your formulas if you paste them as text rather than a screenshot. You can paste them in a codeblock using this tag:
Not surprising there throws an error message. Filter 1,2,3 are based on row-level whereas filter 4 is an aggregation filter. Its SQL equivalent reads,
SELECT [assignee], COUNT(DISTINCT [assignee]) FROM BASE B JOIN Teste_Agents1 T ON B.[assignee]=T.[Nome] AND B.[Setor]=T.[Setor3] --row-level WHERE [assignee] IS NOT Null GROUP BY [assignee] HAVING COUNT([assignee])>7 --aggregation filterconversion of DAX measure,
= COUNTROWS( FILTER( CALCULATETABLE( VALUES( Base[assignee] ), filter1, filter2 ), CALCULATE( COUNTROWS( base ) > 7 ) && NOT ISBLANK( Base[assignee] ) ) )
