topic Re: COUNTROWS with ALL and FILTER in DAX Commands and Tips

COUNTROWS with ALL and FILTER

joshua1990 — Sun, 22 Mar 2020 11:08:03 GMT

Hello everybody!

I have a table with the following structure linked to my data model:

The Date Column is an active connection to the calendar table.

Then I have a Pivot table like this:

This table shows me the sum for each week.

Now I want a second column in this pivot that shows me the total sum regardless of the week, but just for values above 500.

The following measure is not working:

=CALCULATE(COUNTROWS(tblOne);FILTER(tblOne;tblOne[Value]>500);ALL(tblOne))

=CALCULATE(COUNTROWS(tblOne);ALL(tblOne);FILTER(tblOne;tblOne[Value]>500))

How would you solve that?

Greg_Deckler — Sun, 22 Mar 2020 14:35:33 GMT

Perhaps:

Measure = SUMX(FILTER(ALL('Table'),[Value]>500),[Value])

Although I can't fathom why you were using COUNTROWS so if you actually want the count, use COUNTX instead of SUMX.

Anonymous — Sun, 22 Mar 2020 18:03:18 GMT

Or... you could try this as well:

[GT500 Total] = CALCULATE( SUM( Table[Value] ), Table[Value] > 500, ALL( CalendarTable ) )

This one will respect all filters in all dimensions but the ones on CalendarTable. Maybe this is what you wanted?

Best