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

You can use a measure: Blank Count = CALCULATE(COUNTBLANK(Daily Stock[Units]) ,ALL(Daily Stock[Size]) ) This will count all blanks in all sizes

Thanks SteveCampbell, But when I use your formula, I get this,

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] , Contador If this does not work I would need to see the table.

Count blank in matrix | Microsoft Fabric Community

14 Replies

SteveCampbell
Memorable Member
8 years ago
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_
  Frequent Visitor
  8 years ago
  Thanks SteveCampbell,
  
  But when I use your formula, I get this,
  - SteveCampbell
    Memorable Member
    8 years ago
    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]))
Vvelarde
Community Champion
8 years ago
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_
  Frequent Visitor
  8 years ago
  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
    Memorable Member
    8 years ago
    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

Forum Discussion

Count blank in matrix

14 Replies

Related content

Counting Non-Blank Records

Remove Blank Rows in Matrix

Replace blank with 0 in matrix visual

PowerBI - Matrix table blanks

Counting nulls