Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NISH72
Helper IV
Helper IV

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
1 ACCEPTED SOLUTION

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] ) )

 NABlank.jpg

View solution in original post

17 REPLIES 17
v-xicai
Community Support
Community Support

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.

Nathaniel_C
Super User
Super User

Hi @NISH72 ,
Try this. Use = BLANK()

Blank.PNG

 
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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




It's throwing same error

@jdbuchanan71 ?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Error -expressions that yield variant data types cannot be used to define calculated columns

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] ) )

 NABlank.jpg

How to convert. Can you please share. Thanks
Nathaniel_C
Super User
Super User

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.
Nathanielnew york.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Can please someone send the dax for this. Thanks

Hi @NISH72 ,

If (table[columnname] = "","N/a",table[columnname])


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




It worked for city column but I have one more column named sales that also has blank values. When I use for sales column It's throwing error- dax comparison operation don't support comparing numbers with text.

Hi @NISH72 ,

Try using null instead of "" for that 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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




If (ISBLANK(table[columnname]) ,"N/a",table[columnname])

Hi @NISH72 , or this.
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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @NISH72 ,you may need to check your data type for that column.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




The sales column has numeric values. Thanks

It's throwing error- expressions that yield variant data types cannot be used to define calculated columns

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.