cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jderekc
Helper IV
Helper IV

DAX - Variant Data Type error IF statement

Hi all,

 

I am writing a DAX formula to return a text result ("NED", which means "not enough data") if a quantity on hand of an item is less than a certain amount.  However, if it is true, then it should return the numerical data.  I don't care if the data is actually stored as a number or a string, however, as I am just displaying it in a card in Power BI desktop.  Though the data IS numerical in previous calculations (hence why this final calculation is called "Calc4_qOH").  Here's my query:

 

EstQtyOH = IF(InvAnalytics[Calc4_qOH] < 2, "NED", InvAnalytics[Calc4_qOH])

 

I receive the error "Expressions that yield variant data-type cannot be used to define calculated columns."

 

If I change "NED" to a number, the IF statement works.  I tried SWITCH as well, but didn't get any further than if I used IF.  Am I doing something wrong and my request is possible, or is my request impossible for DAX?  Again, I am still learning DAX as I go.  Many thanks in advance!

 

- Derek

 

1 ACCEPTED SOLUTION

Hey,

 

try this 

 

IF(
  AND(
    InvAnalytics[Calc4_qOH] >= 2 
    ,NOT ISBLANK(InvAnalytics[Calc4_qOH])
  )
  ,FORMAT(InvAnalytics[Calc4_qOH], "#.##")
  , "NED"
) 

 

this could be an alternative

 

IF(
  AND(
    InvAnalytics[Calc4_qOH] >= 2 
    ,InvAnalytics[Calc4_qOH] <> ""
  )
  ,FORMAT(InvAnalytics[Calc4_qOH], "#.##")
  , "NED"
)

 

BLANK() returns a BLANK() meaning NULL value whereas ISBLANK(...) checks if a column reference is empty

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

18 REPLIES 18
Anonymous
Not applicable

I am trying to use if statement to calculate a column based off of 2 other columns.  There can be different combinations that the two columns can be to give me different outputs.  I shortened my dax statement below to get the point across, but I keep getting an error that says "Expressions that yield variant data-type cannot be used to define calculated columns".

 

Risk Category =
If(ARPM[Severity]="Near Miss" && ARPM[Likelihood of Occurrence]="Frequent", "Priority Risk",
if(ARPM[Severity]="Near Miss" && ARPM[Likelihood of Occurrence]="Occasional", "Priority Risk",
if(ARPM[Severity]="Near Miss" && ARPM[Likelihood of Occurrence]="Uncommon", "Recognized Risk",
if(ARPM[Severity]="Near Miss" && ARPM[Likelihood of Occurrence]="Remote", "Recognized Risk", 0))))

 

Please help!

TomMartens
Super User
Super User

Hey, you can't use in DAX, but you can use a DAX statement, that explicitly converts the numeric value to text, like so

IF(
	'Table1'[Column1] >= 2, FORMAT('Table1'[Column1],"#"), "too few")

Hope this helps

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hey Tom, that helps a lot, thanks!  I am having problems dealing with blank values/NULLs.  I tried to use the following but anything that comes across as blank will not show "NED" (in your case "too few") and instead shows "(Blank)":

 

EstQtyOH = IF(InvAnalytics[Calc4_qOH] >= 2 && NOT BLANK(), FORMAT(InvAnalytics[Calc4_qOH], "#.##"), "NED") 

 

Hey,

 

try this 

 

IF(
  AND(
    InvAnalytics[Calc4_qOH] >= 2 
    ,NOT ISBLANK(InvAnalytics[Calc4_qOH])
  )
  ,FORMAT(InvAnalytics[Calc4_qOH], "#.##")
  , "NED"
) 

 

this could be an alternative

 

IF(
  AND(
    InvAnalytics[Calc4_qOH] >= 2 
    ,InvAnalytics[Calc4_qOH] <> ""
  )
  ,FORMAT(InvAnalytics[Calc4_qOH], "#.##")
  , "NED"
)

 

BLANK() returns a BLANK() meaning NULL value whereas ISBLANK(...) checks if a column reference is empty

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks for the prompt reply.  Sadly the issue persists with showing up as "(Blank)".  It doesn't make sense to me as it should have worked from the beginning.  I CAN attest to the fact it works if I increase the value to compare against (e.g.: make "2" a "500" and all values lower than "500" show up as "NED"), but it just doesn't want to work with blanks.

@jderekc,

 

You may illustrate with some dummy data or post some screenshots.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I can try to do that.  There's quite a bit of data, though, so I'm unsure the best way of presenting a lot of dummy data, so selective screenshots may be useful.  What data in particular needs to be looked at?  Table relationships?  I did mention that in my previous post.  Let me know.  Thanks!

 

- Derek

I believe the problem with the "BLANK"s is that sometimes I am dividing by NULL data (no data available).  I've tried throwing in IFERROR and ISBLANK but can't seem to convert any divide-by-null operations into plain zeroes.  Is this possible?  Thanks!

 

- Derek

Look here: https://msdn.microsoft.com/en-us/library/jj677276.aspx



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks, that was very helpful.  It didn't solve the problem, but that's okay.  I learned something new.  🙂

 

I took a look at the data from the table and it is strange.  I don't see any BLANKs in the table from my estimated quantity-on-hand calculated column.  I see my "NEDs" and those with values, but not BLANKs.  I wonder how those BLANKs are getting thrown into the BI desktop then.  I'll see if I can get some dummy data to give you to review.  Thanks so much!

 

Best regards,

Derek

It looks like it is because those darn items aren't even in the new table I created!!!!  So of course they are blank!  They do not exist in the table the calculations are in whatsoever.  Sorry about that.  I might need to change my natural inner joins to some outer joins via DAX.

@jderekc,

 

Any progress, feel free to share with us.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Basically it comes down to products sold in a certain date range.  In SQL in my WHERE clause, I have the date ranges set that I want to query against.  Instead of tens of thousands of potential product matches, I am under ten thousand for a particular date range.  The items in my Power BI report include ALL products.  Thus, since a product isn't found in my query (not even a NULL value), then I get a blank return.  I think this is just how it's going to have to be.  I don't know how to force show "NED" for all products when what I am comparing against doesn't have a matched product in the first place.  It simply doesn't exist in those date ranges.

 

Thanks for your help!  I really appreciate it.

 

Regards,

Derek

@jderekc,

 

A simplified example would help a lot.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hey guys, I got it fixed.  I simply combined my three queries into one using derived tables/subqueries.  Now all blanks show as "NEDs".  Thanks for your help!

Hey,

 

posting screenshot is useful to some extent, but I personally like data.

Posting scrrenshots, puts a lot of burden to the people who like me also need data, to better understand your requirement, by typing the data. For this reason I appreciate if you would use the "Enter Data" funtion to create a simple tabel model, enter data manually or copy some data columnwise from excel, also create the relationships.

 

You entered enough data when the issue is reproducable 🙂

 

Upload the Pbix file to onedrive or dropbox (for some policy reason, personally I just can use these both) and share the link here.

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hey, can you share some sample data, by sharing a link that points to the file on onedrive or dropbox.

 

Is there a relationship to the calculated column?

"(Blank)" shows up, when a column is used on a visual and this column is on the one-side of a relationship and the corresponding column on the many-side contains more values (meaning other values) than the column on the one-side.

 

Regards

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks, Tom!  I unfortunately cannot share the data as it is sensitive to my company.  Not that anyone on here would care, but just know that I am unable to share this particular set of data (contains sales, margins, inventory, vendors, et cetera).  It's okay if we can't figure it out as I'll keep plugging away.  🙂

 

As far as the relationships:  this calculated column is from a table I created via DAX using NATURALINNERJOINs.  I had three separate SQL queries that were tough--and not readily apparent how--to combine into one, so I used that.  The relationship for the table is a one-to-one relationship between the "Items" master table and the "InvAnalytics" table.  The cross filter direction is set to "both".  The relationship is active.

 

Here's a breakdown of some of my tables:

 

Table: Cases_PYN30Days

Columns: PYNItem, CasesPYN30

 

1-to-1 relationship with Cases_CY90PDays

 

Table: Cases_PY90PDays

Columns: PYItem, CasesPYP90

 

1-to-1 relationship with Cases_CY90PDays

Many-to-one relationship with StockOH

Many-to-one relationship with StockOHRep

Many-to-one relationship with Specials

 

Table: Cases_CY90PDays

Columns: CYItem, CasesCYP90

 

1-to-1 relationship with Cases_PY90PDays

1-to-1 relationship with Cases_PYN30Days

 

Table: Cases_OnHand

Columns: OHItem, QTYONORD, QTYONHND

 

1-to-1 relationship with Cases_PYN30Days

 

Table: InvAnalytics

Columns: PYItem, CasesPYP90, CYItem, CasesCYP90, PYNItem, CasesPYN30, Calc1_PCT, Calc2nPY, Calc3_pDay, OHItem, QTYONORD, QTYONHND, Calc4_qOH, EstQTYOH

 

1-to-1 relationship with Items table

 

 

Best regards,

Derek

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.