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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kreg
Helper I
Helper I

Query does not work as expected

Hi,

 

I have a query which works partially:

Model Type =
 IF (LEFT(model[type], 8) = "https://" ||
 OR(
    ISNUMBER(model[type])
    ,0
    ),
    "Accepted","Failed")
 
Now, you can imagine that someone provide a full link to car model field which is a "type" i.e. https://tesla.com/345345 or only 345345 and both of that inputs should be treat aa Accepted, but now only hyperlink goes to the Accepted bucket, but number - based on the above query - still to the Failed bucket. It is only provided in the first place, so I thought that part with ISNUMBER solves that issue, but not. How should I change it?




1 ACCEPTED SOLUTION

@Kreg ,

Here is a slight modification:

 

 

Accepted/Failed = SWITCH(
                      TRUE(),
                      LEFT( [Type],8 )   = "https://", "Accepted",
                      LEFT( [Type], 1 ) IN {"1","2","3","4","5","6","7","8","9","0"}, "Accepted",
                      "Failed" )

 

 

Type                                       Accepted/Failed

https://tesla.com/345345 Accepted
345345 Accepted
- Failed
Not Applicable Failed

The limitation with this code is that it assumes if the first character is a digit, then the entire value is numeric.  If your entries will consist of both numbers and letters, then further adjustments will have to be made.

Regards,

View solution in original post

4 REPLIES 4
rsbin
Super User
Super User

@Kreg ,

Looks like there are a few issues with your syntax.  Please try this:

 

Accepted/Failed = SWITCH(
                      TRUE(),
                      LEFT( [Type],8) = "https://", "Accepted",
                      ISNUMBER( VALUE([Type] )) = TRUE(), "Accepted",
                      "Failed" )

 

 

rsbin_0-1685130611427.png

I trust I interpreted your question correctly.

Regards,

You interpreted it very correctly but I missed that in some rows might be some string provided by the user like "-", "Not Applicable", etc. and when I used your query I got message:
"Cannot convert value '-' of type Text to type Number."

So lets say that everything those kind of input must be treated as Failed. 

@Kreg ,

Here is a slight modification:

 

 

Accepted/Failed = SWITCH(
                      TRUE(),
                      LEFT( [Type],8 )   = "https://", "Accepted",
                      LEFT( [Type], 1 ) IN {"1","2","3","4","5","6","7","8","9","0"}, "Accepted",
                      "Failed" )

 

 

Type                                       Accepted/Failed

https://tesla.com/345345 Accepted
345345 Accepted
- Failed
Not Applicable Failed

The limitation with this code is that it assumes if the first character is a digit, then the entire value is numeric.  If your entries will consist of both numbers and letters, then further adjustments will have to be made.

Regards,

Thats IN really works and looks very smart. Thanks for that. It works for me.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors