Count blank in matrix

[ivancornejo_]

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

Regards,

Iván.

PS: Sorry for my English

[Vvelarde]

@ivancornejo_

Hi, please try these steps:

1. Create a New Table Sizes: Modeling-New Table

Sizes = VALUES(Table1[Size])

2. Related to Original Table (In my case Table1)

3. Create the nexts measures:

AllSizes = COUNTROWS(ALLSELECTED(Table1[Size]))

SizeAvalaible = COUNTROWS(VALUES(Table1[Size]))

NoSizeAvailables-Total = SUMX(VALUES(Table1[Article]),CALCULATE([AllSizes]-[SizeAvalaible]))

4. Create the Matrix

Showing the measures AllSizes and SizeAvailable only to demostration purpose.

5. Use a card with the measure:  NoSizesAvailable-Total

Regards

Victor

Lima - Peru

[ivancornejo_]

Thanks @Vvelarde

I follow your step and I get this,

 But when I change 

AllSizes = COUNTROWS(ALLSELECTED(Table1[Size]))   --> AllSizes = COUNTROWS(ALLSELECTED(Size[size]))

Matrix show this,

As you can see, Total for (NoSizeAvailable-Total) is correct, I have 8 Articles in Blank , but the others numbers are incorrect 

and the same with the others measures 😕 

[SteveCampbell]

Did you try changing 

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

What was the result? If you could send a the pbix file that would help a lot, depending on the data

[ivancornejo_]

@SteveCampbell Yes

Result: 

Total in a single "Contador" show incorrect values,  but in the last "Contador" that is correct, but the total show 1, and it is incorrect  😧 

I'll tried send the pbix file

[v-huizhn-msft]

Hi @ivancornejo_,

Where is your .pbix file? Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Best Regards,
Angelia

[ivancornejo_]

Oh, sorry

I was very busy last week.

here is the .pbix file:
https://drive.google.com/file/d/1PLzB6IflxMZR8128BV_M31BAQ0Q4JN7d/view?usp=sharing

and still I can't resolve this 😕 

thanks.

[v-huizhn-msft]

Hi @ivancornejo_,

Based on my understanding, the first visual is right. For second visual, what result you want to get, how about using the formula below?

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

Best Regards,
Angelia

[SteveCampbell]

You can use a measure:

Blank Count =
CALCULATE(COUNTBLANK(Daily Stock[Units])
                    ,ALL(Daily Stock[Size])

)

This will count all blanks in all sizes

[ivancornejo_]

Thanks @SteveCampbell,

But when I use your formula, I get this,

[SteveCampbell]

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]))

[ivancornejo_]

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.

[SteveCampbell]

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_]

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]

Can you try 

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