Forum Discussion

elopez's avatar
elopez
Icon for Helper I rankHelper I
6 years ago
Solved

Replacing null values in matrix visual

Hi,    I created a table using the matrix visual. My values are sums of number of records in a field (named 'Faculty Rank' and once I drag that field under values, I choose the Count option). I wan...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi elopez ,

     

    Based on my research, If your table does not have at least one row record in the blank cell (such as our following example), the measure will not calculate at all:

     

     

    We can use a separated table such as following to meet your requirement

     

    Calculated Table:

     

    Column = DISTINCT('Table'[Column])

     

     

    Measure:

     

    Measure Value = CALCULATE([Measure]+0,'Table'[Column] in DISTINCT('Column'[Column]))

     

     

     


    By the way, PBIX file as attached.


    Best regards,

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago
Measure = IF(ISBLANK(SUM('Table'[Faculty Rank])),0,SUM('Table'[Faculty Rank]))
  • elopez's avatar
    elopez
    Icon for Helper I rankHelper I
    6 years ago

    I am new to power BI so maybe i did this wrong? I: clicked the table-->New measure-->wrote in what's in the screenshot-->dragged the newly created 'number of faculty' measure to my values field. but then the table couldn't be displayed (the table is very similar to the 2nd table below it. i want to replace all the blank values with '0'

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Wait, is that measure you are pointing to what is being used in your current table visualization? If that is the case:

       

      Measure = IF(ISBLANK([your current measure name]),0,[your current measure name])

       

      Can you click on See details and tell us what it says?

      • elopez's avatar
        elopez
        Icon for Helper I rankHelper I
        6 years ago

        based on your 2nd message, i am unsure if i need to change what i wrote in the original measure?

    • elopez's avatar
      elopez
      Icon for Helper I rankHelper I
      6 years ago

       

      so i created the measure and named it number of faculty (from your first message). then i dragged that to the values section. is this correct and the problem is the string type?