Error with Conditional Statement

Trying to setup a breach alert/indicator to show if a particular request breach the SLA period set for it. So, what i did was writing a IF condition as seen below but i am getting an error

image

Is it because the value i am using as the output is a string value and not a whole number, how can i correct it?

@Melissa, Will appreciate your input on this as well

@aroloyefolajimi,

Try wrapping your text value with VALUE() to convert it to a number.

Note: this happens most frequently when your field type is mixed text/numeric. If possible, try to avoid that field type and pick one or the other.

  • Brian
1 Like

What is the error that you are getting? Is this a measure or a calculated column?

@AntrikshSharma Tried both Measure and Calculated Column but got the same error response

Can you show us the error details?

@AntrikshSharma Sorry i forgot to add the error response

For Calculated Column, this is the error i am getting

and For measures, this is the error

Thanks @BrianJ Will try it out

Alright, on behalve of all forum members :wink: can you share a picture of your model. Clearly depicting all relationships?

And are you referencing naked table[column]'s again or are these measures we’re looking at?
Thanks!

@Melissa

From the Model, you can see that there is a relationship between SLA Table and Complaint Table,

Look forward to your feedback

@aroloyefolajimi,

Now seeing your specific error message, my solution is not going to solve your problem. Per @Melissa’s reply, we will need more info. Screenshot of model is OK, full PBIX file is better…

  • Brian

The error in the calculated column is because you have a many to many relationship between the two tables SLA and Complaint, while RELATED is used to fetch the information from the table that is on the one side of the relationship.

The error in the measure is because you have a naked column reference, I would have used SELECTEDVALUE but that’s my assumption of what you are trying to do. Also RELATED won’t work here as you need a row context for RELATED to work.

1 Like

@BrianJ @Melissa @AntrikshSharma Drafted a sample file, the PBIX file is below

Test.pbix (1.5 MB)

Look forward to your feedback

Replace the NA with 0 in the column SLA[Dev SLA(Days)] and then Change the data type of column to whole number post that you can try something like this: it is upto you to check for MAX or MIN SLA or between both.

Column = 
VAR A = MAXX( RELATEDTABLE( SLA ), SLA[Dev SLA(Days)] )
VAR Result =
IF ( Complaint[Age] > A, "Breached", "Unbreached" )
RETURN
    Result

@AntrikshSharma It worked :grin:, thank you very much. Please can you explain why you used MAXX( RELATEDTABLE( SLA ), SLA[Dev SLA(Days)] )? instead of just selecting the Column SLA[Dev SLA(Days)] , trying selecting the column alone but it was not showing

Because your SLA Table has many values for each row of Complaint, in this case you can only use RELATEDTABLE, RELATEDTABLE provides you all the rows of the SLA table that are related to complaint, and I used MAXX to get the max value out of all the rows that are being returned by RELATEDTABLE, other wise you would be comparing 1 age from Complaint against many SLA Days. To be honest Many to Many relationship is not a good idea.
I did basic data modelling for you and you can use this file, your original code now works on this one.
SLA.pbix (48.4 KB)

@AntrikshSharma Thanks for the help, really appreciate it

@AntrikshSharma For the two solution you sent to me i.e the DAX Formula and the PBIX file,which one do you recommend I use?

I am asking because no other Cardinality is working, it is only Many to Many that it is accepting, you know what I sent to you was a test and Complaint table actually has other columns. Please, what do you advise?

You should use PBI file that I have sent

Ok, just tried recreating what you did on the main report and while creating the relationship between SLA and Complaint Table, it was only accepting Many - Many

image

The error above was what i was getting for the rest

Because your SLA table has duplicates you need to remove duplicate rows in power query or the data source.