Forum Discussion

ivancornejo_'s avatar
ivancornejo_
Frequent Visitor
8 years ago

Count blank in matrix

Hi, I have this matrix ->     I need count the blank spaces in a new column or use a measure. I tried a lot of possibles solutions from the forum, but a can't get a solution  Help, please   ...
Need Help
SteveCampbell's avatar
SteveCampbell
Icon for Memorable Member rankMemorable Member
8 years ago

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] , Contador

 

 

 

If this does not work I would need to see the table.

ivancornejo_'s avatar
ivancornejo_
Frequent Visitor
8 years ago

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 :( 

  • SteveCampbell's avatar
    SteveCampbell
    Icon for Memorable Member rankMemorable Member
    8 years ago

    Can you try 

    Contador :=   
	if(
		COUNTROWS('Article')*COUNTROWS(Size)-COUNTROWS('Stock Price')<1,
			0,
			COUNTROWS('Article')*COUNTROWS(Size)-COUNTROWS('Stock Price')
		)