Forum Discussion

NISH72's avatar
NISH72
Helper IV
6 years ago
Solved

Show null values as na

Hi I have a city column with some blank values in it. What will be the dax for new calculated column so that if the value is blank in City column it will show as "n/a" in new calculated column and if it's not blank it will show the exact values. The new calculated column should be like this. Thanks

Calculated column
New York
N/a
Atlanta
N/a
Need Help
Show and Tell
Tips and Tricks
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    You have to convert the output of the non-blank to text as well in order for it to display "N/A" on the blanks.

    Column = IF ( ISBLANK ( 'Table'[Sales] ), "N/A", TRIM ( 'Table'[Sales] ) )

     

17 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi NISH72 ,

    Best to go to Power Query, Add Column, Conditional Column. In your case Value and Column1 will be the same column.
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

     

    • NISH72's avatar
      NISH72
      Helper IV
      6 years ago
      Thank you for your reply. I can't use power query as I am using a live connection. I want to create a dax in ssas model. Can you please let me know the dax for this. Thanks
      • NISH72's avatar
        NISH72
        Helper IV
        6 years ago
        Can please someone send the dax for this. Thanks
  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi NISH72 ,
    Try this. Use = BLANK()

     
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi NISH72 ,

     

    If you connect to SSAS using connect live mode in Power BI Desktop, then you can only create report level measures instead of calculated columns. So you may create measure like DAX below, and display it in table visual.

     

    Measure1= IF(MAX(Table1[City])=BLANK(),"n/a", MAX(Table1[City]))

     

    Best Regards,

    Amy

     

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