Forum Discussion
Countrows as a filter within a CALCULATE function
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] ) ) )
Super UserAnonymous
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
Appreciate your reply, @jdbuchanan71, but it didn't work. Is there any way I can do it inside the formula I showed in the image above?
- jdbuchanan71
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: