DAX to return seperate values for 0 and BLANK

[smpa01]

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.

[amitchandak]

@smpa01 , try new column

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

[smpa01]

@amitchandak thanks it worked

[harshnathani · Accepted solution]

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!!

[smpa01]

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