Forum Discussion
Count blank in matrix
I am wondering if your values are truely blank. What do blank values appear as in the table?
Can you try the measure:
Contador :=
CALCULATE(
COUNTROWS('Daily Stock')
,NOT('Daily Stock'[Units]>0)
,ALL('Daily Stock'[Size]))
Thanks again, SteveCampbell, but I still get the same result with this other formula
The values in blank show an article without units for a size.
I see - there are missing rows for the blanks, and these records do not exist?
This is much harder, the reason they are blank is that these records do not exist, so you cannot count them (they are not blank, simply not there).Solution:
Create a table on Modelling > New Table:
Article = VALUES('Stock Price'[Article])Create another table on Modelling > New Table:
Size = VALUES('Stock Price'[Size])Join both tables to stock price (Article to Article and Size to Size).
Then, in Stock Price create a measure:
Contador := COUNTROWS('Article')*COUNTROWS(Size)-COUNTROWS('Stock Price')Create a matrix,
For ROW: Article[Article]
For COLUMNS : Size[Size]
For Values ' Stock Price'[Units] , ContadorIf this does not work I would need to see the table.
Thanks^2 SteveCampbell,
I follow the steps and I get this
Yes, now I have 1 in my matrix for an article without units for a size, but as you can see, the articles with units and size, the measure show a number like 0 or negative number, but never is greater than 1, now just need sum those 1 but I can't resolve :(
Can you try
Contador := if( COUNTROWS('Article')*COUNTROWS(Size)-COUNTROWS('Stock Price')<1, 0, COUNTROWS('Article')*COUNTROWS(Size)-COUNTROWS('Stock Price') )