DAX - Variant Data Type error IF statement

[jderekc]

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

[Anonymous]

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]

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

[jderekc]

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

[TomMartens · 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

[jderekc]

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.

[v-chuncz-msft]

@jderekc,

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

[jderekc]

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

[jderekc]

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

[TomMartens]

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

[jderekc]

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

[jderekc]

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.

[v-chuncz-msft]

@jderekc,

Any progress, feel free to share with us.

[jderekc]

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

[v-chuncz-msft]

@jderekc,

A simplified example would help a lot.

[jderekc]

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!

[TomMartens]

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

[TomMartens]

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

[jderekc]

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