Reply
smpa01
Super User
Super User
Partially syndicated - Outbound

DAX to return seperate values for 0 and BLANK

Helllo guys,

 

Is there any way for me to tell DAX to distinguish between BLANK and 0. If I run BLANK() DAX converts all cells with blank to 0 and BLANK() returns true for both 0 and blank.

 

e.g.

 

My raw data is following

 

CAT Value
A1  
A2 0
A3 0
A4 100
A5 100
A6 1
A7  

 

Currently it returns

CAT Value Current Result
A1   blank
A2 0 blank
A3 0 blank
A4 100 non-blank
A5 100 non-blank
A6 1 non-blank
A7   blank

 

Is there any way for me to tell DAX to return the following as my desired result

 

CAT Value Desired result
A1   blank
A2 0 non-blank
A3 0 non-blank
A4 100 non-blank
A5 100 non-blank
A6 1 non-blank
A7   blank

 

I tried with both

BLANK

and

COALESCE

but I could not make DAX to return what I wanted.

 

The sample PBIX is attached. if someone can please help would be great.

 

Thank you in advance.

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion

Syndicated - Outbound

HI @smpa01 ,

 

Please check if the below video can be of any help.

 

https://www.youtube.com/watch?v=C26DQkb4hyY

 

 

Regards,

Harsh Nathani

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

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

Syndicated - Outbound

@smpa01 , try new column

if(isblank([Value]) ,"blank","non-blank")

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Syndicated - Outbound

@amitchandak thanks it worked

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
harshnathani
Community Champion
Community Champion

Syndicated - Outbound

HI @smpa01 ,

 

Please check if the below video can be of any help.

 

https://www.youtube.com/watch?v=C26DQkb4hyY

 

 

Regards,

Harsh Nathani

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

Syndicated - Outbound

@harshnathani thanks mate, it worked. == did the trick.

 

Capture.PNG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
avatar user

Helpful resources

Announcements
FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)