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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
UserInterface
Advocate I
Advocate I

Show if field is blank or not.

Hi I have just installed Power BI for a test and am stuck straight away.

I have a field that I wish to report if it is empty or not. (Something like 20% Missing, %80 Found)

 

I initially thought that I could just apply a filter on a Pi chart but couldn't work that out, so tried to add a new column the same way I would in excel but that fails too. Can anyone put me on the right path?

 

I tried the following 

IF([ATTRIBUTE_2401],"","Missing","Found")

But get the error Expression.Error: The name 'IF' Wasn't recognized. 

 

I also tried with ISBLANK BLANK and a few others.

1 ACCEPTED SOLUTION
SivaMani
Resident Rockstar
Resident Rockstar

Can you try this?

 

IF([ATTRIBUTE_2401] = "","Missing","Found")

View solution in original post

12 REPLIES 12
QuantumWest
Regular Visitor

Today this works like so:

 

if [ATTRIBUTE_2401] = "" then "No"
else "Yes"

SivaMani
Resident Rockstar
Resident Rockstar

Can you try this?

 

IF([ATTRIBUTE_2401] = "","Missing","Found")

I tried this one first and it works, i guess i was just missing the = sign.. i don't rememebr using that before but it had been a long time.

 

Thanks!

Great!

 

Please mark it as a solution and hit kudo button if it is helpful.

 

Thanks,

Siva

Actually I spoke to soon.

When I try the same soultion on another field (different table) that contains the same data it doen't find the blanks, although it is reporting the others.

 

I tried both 

Status = IF(isBlank([UDF_CHAR1]),"Missing","Found")

and

Status = IF([UDF_CHAR1] = "","Missing","Found")

I don't get any errors, its just is not showing the blanks.

 

The only difernece between the fields is one is nvarchar(3500) and other is nvarchar(250) otherwise the have been poulated with the same script.

 

Screenshot where you can see the orginal working one, and the new one looking at the new field.

https://imgur.com/gxo0Vv8

Anonymous
Not applicable

Check what the actual contents of the table are.  In data terms, null, "", and " " are all considered different.  Something as a simple as an extra space could be the cause for an issue.

If i look in SMS then it just says NULL, but i tried that too.

After i made that last post i found that i can see that actual data in powerBI, so took a look at that field and found that it is not displaying any of the blank rows, however, I can see them in the DB

 

udf_char1 is the new field.

 

DAX2.png

 

When i look at the data in PowerBI then this field doesn't show any null values, even though it does for the other rowsdax3.PNG..

 

 

Anonymous
Not applicable

Locate a row from your Data Source that contains a null and locate that same row in Power BI.

 

Could your SQL code's logic be excluding the rows with null?

Anonymous
Not applicable

Did you import the column with a different name?  Looking at your SQL, should your code be:

 

Status = IF(isBlank([Imported MEF Expiry Date]),"Missing","Found")

What other data manipulation is occuring in your import that concerns this field?  Try getting a dump of the table, as it sits in Power BI, into Excel and look at the contents.

You pointed me in the right direction.. I had tunnel vision and didn't notice that i am joining on workstationID, but if there is no date, then there is no ID... 

 

Not sure how i'll get around this one, but I can figue something out now.

 

dax5.png

Anonymous
Not applicable

I'm assuming you are created a new column using DAX.  If so you need to name the column.  It should look something like this:

 

 

FoundTag = IF(
 isBlank([ATTRIBUTE_2401]),
 "Missing",
 "Found"
)

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.