Forum Discussion

AmyD's avatar
AmyD
Icon for Helper I rankHelper I
3 months ago
Solved

M Formula - Show 0 instead of (blank)

Hi All,

 

Hopefully I've posted this in the right location. I'm after an M code that I can then use on a card to show 0 instead of (blanks).

I don't have access to Power BI Desktop - I'm purely working in the online version and going "open semantic model" and "transform data" in Power Query.

 

I've tried using this column but still returning blanks

if [Unique Reference] = null then 0 else 1

 

Any ideas?

Many thanks

Need Help
  • MasonMA's avatar
    MasonMA
    3 months ago

    If you have Editing permission you should be able to create DAX Measure in Service after you 'Open Semantic Model'.

    A measure like COALESCE([Measure], 0) will work. 

     

7 Replies

  • mussaenda's avatar
    mussaenda
    Icon for Community Champion rankCommunity Champion
    3 months ago

    Hi AmyD,

     

    First of all, DAX and Power Query are two different things.

    Second, if you are only able to access the semantic model, can you download the the report?

    If you have "" empty strings, does it mean that your data type is string? if that's a yes, proceed with cengizhanarslan's suggestion by creating a new measure. if your data type is number then create a mesaure then call your current measure then add a +0 to it. 

    =[current_measure]+0

     

    Now.. if you can't add a measure, you can use set a value for blank values in the card itself. "Show blank as" option and put 0. 

     

    Hope this helps.

  • AmyD's avatar
    AmyD
    Icon for Helper I rankHelper I
    3 months ago

    I accepted as a solution by mistake! I didn't think I can use DAX in Power Query? I don't have acecss to Power BI Desktop

    • MasonMA's avatar
      MasonMA
      Icon for Super User rankSuper User
      3 months ago

      If you have Editing permission you should be able to create DAX Measure in Service after you 'Open Semantic Model'.

      A measure like COALESCE([Measure], 0) will work. 

       

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    3 months ago

    In M a blank is different from a null value. 
    Your column formula,

    [Unique Reference] = null then 0 else 1

    would replace any null values with a 0. 
    Blanks are generally referenced by "".

    Try this,

    [Unique Reference] = "" then 0 else 1
  • cengizhanarslan's avatar
    cengizhanarslan
    Icon for Super User rankSuper User
    3 months ago

    If it still returns blanks, the issue is likely that your column contains empty strings ("") rather than actual nulls. These look the same in the UI but are handled differently. Replace your formula with:

    if [Unique Reference] = null or [Unique Reference] = "" then 0 else 1

     

  • v-sgandrathi's avatar
    v-sgandrathi
    Icon for Community Support rankCommunity Support
    3 months ago

    Hi AmyD,


    Thank you mussaenda cengizhanarslan jgeddes and MasonMA for your prompt responses to the query.

    Following up to check whether you got a chance to review the suggestions given. If the issue still persists please let us know. Glad to help. 

     

    Thank you and Continue using Microosft Fabric Community Forum.