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
Vvelarde's avatar
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_'s avatar
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's avatar
    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

    • ivancornejo_'s avatar
      ivancornejo_
      Frequent Visitor
      8 years ago

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

       

      I'll tried send the pbix file

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        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