Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

replace blank with 0 in Matrix

Hi,

I am new to PowerBI and having an issue displaying 0 instead of blank.

 

I have a matrix,

colum: Month (select from Date Hierarchy)

row: Area

values: Measure (Calculate(DISTINCTCOUNT.....)

 

However, I need to replace the blank cells with '0'and the month should be short name. (MMM)

I can only accomplish one of my requirement. That said, I can display 0 (by adding +0  at the end of my measure) with the Month column from date hirerarchy; but when I swith to short month column (creating by Format function), the 0 become blank.

 

I have tried a lot of tricks I could find in the threads. Still, I cannot make both requirements at the same time.

 

Please kindly suggest what can I do.

 

 

Need Help
Tips and Tricks
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    It’s my pleasure to answer for you.

    According to your description, I think the 'add 0' method is fine, I also tried it. But if it isn't used on a  date table, your problem will occur. You can create a date table, set a relationship between them, and use your formula:short_mon = FORMAT(Table [Date], "mmm").

    Table =
CALENDAR ( MIN ( TABLE[DateCompleted] ), MAX ( TABLE[DateCompleted] ) )

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Anonymous what measure you are using, add 0 to it

     

    for example

     

    Measure = DISTINCTCOUNT ( Table[Column] ) + 0

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi,

      Thanks for sharing. 

      Actually, I have tried this and it worked if I use the Month column from date hierarchy. But when I switch to month short name column, it doesn't work. 

      I have also tried IF(ISBLANK....) and some other methods. None of those can solve both situations.  

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Anonymous not sure what you mean by short month and it doesn't work, that is probably some other issue. What is short month? Is it from the calendar/date table? Provide bit more details on it.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi,

      It's a new column I created to fulfill the MMM format month requirement.

      Here's the formula:

      short_mon = FORMAT( [DateCompleted], "mmm")
      • v-janeyg-msft's avatar
        v-janeyg-msft
        Community Support
        5 years ago

        Hi, Anonymous 

         

        It’s my pleasure to answer for you.

        According to your description, I think the 'add 0' method is fine, I also tried it. But if it isn't used on a  date table, your problem will occur. You can create a date table, set a relationship between them, and use your formula:short_mon = FORMAT(Table [Date], "mmm").

        Table =
CALENDAR ( MIN ( TABLE[DateCompleted] ), MAX ( TABLE[DateCompleted] ) )

        If it doesn’t solve your problem, please feel free to ask me.

         

        Best Regards

        Janey Guo

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.