Using CALCULATE when filter removes all data

jgtirz — Thu, 10 Nov 2022 11:49:00 GMT

I got the following example

Provider	Project	Licitation	Order
1	A	X	1
1	A	-	2
1	B	Y	3
2	B	-	-
2	B	Z	-
3	A	-	4
3	C	-	5
3	C	-	6

And i want a matrix/pivot table that counts for each provider how many projects/licitations/order has been involved in (not counting it therefore when a "-" appears).

The thing is that if I filter out directly these value, whenever a "-" appears for any column it will filter out all the row. Counting only the rows that contains value in all three columns (not even that because provider 3 should get all 0 based on that logic...):

PROVIDER	PROJECTS	LICITATIONS	ORDERS
1	2	2	2
2	1	1	1
3	1	1	1

Using a new measure using =CALCULATE for each column throws an error, because for example for provider 3 if you filter out "-" on licitation you filter out everything and gets empy.

How would I fix it?

The final result should look like:

PROVIDER	# of PROJECTS	# of LICITATIONS	# of ORDERS
1	2	2	3
2	1	1	0
3	2	0	3

Re: Using CALCULATE when filter removes all data

Greg_Deckler — Thu, 10 Nov 2022 12:15:51 GMT

jgtirz Maybe 3 measures:

# of PROJECTS = COUNTROWS('Table') + 0 # of LICITATIONS = COUNTROWS(FILTER('Table',[Licitation]<> "-")) + 0 # of ORDERS = COUNTROWS(FILTER('Table',[Order] <> "-")) + 0

Re: Using CALCULATE when filter removes all data

jgtirz — Thu, 10 Nov 2022 12:28:15 GMT

Cheers mate, that "+0" did the trick!

topic Re: Using CALCULATE when filter removes all data in DAX Commands and Tips

Using CALCULATE when filter removes all data

Re: Using CALCULATE when filter removes all data

Re: Using CALCULATE when filter removes all data