Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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 filter
conversion of DAX measure,
=
COUNTROWS(
FILTER(
CALCULATETABLE( VALUES( Base[assignee] ), filter1, filter2 ),
CALCULATE( COUNTROWS( base ) > 7 ) && NOT ISBLANK( Base[assignee] )
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 filter
conversion of DAX measure,
=
COUNTROWS(
FILTER(
CALCULATETABLE( VALUES( Base[assignee] ), filter1, filter2 ),
CALCULATE( COUNTROWS( base ) > 7 ) && NOT ISBLANK( Base[assignee] )
)
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thank you very much. It was exactly what I was looking for.
@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
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?
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:
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |