If function eith OR "||" in a measure in Tachometer to cover more than 2 condition

[Anonymous]

Hi,

I have a Tachometer that I need to define two conditions:

1- if Target+Actual = 0 then the gauge should be disappeared

2- if Actual > 1.15 * Target then it should be disappeared as well and this is when another Tachometer should kick in and show the status of it.

to achieve the two situation above I though of two Tachometers on top of each other. For the first one I defined following condition:

Alert = if ( [actual] < 1.15 * [target] || [target] + [actual] <> 0, 1, Blank()).

WhenI remove either of "[actual] < 1.15 * [target]" or "[target] + [actual] <> 0" it works perfectly and the gauge disapear as I wished but when putting them together in "Alert measure" like what I did above, it doesn't work . I have inspected the data types (i.e. evertything should be Whole number format) to make sure it works but still it is not working!

Would appreciate your help!

[TeigeGao]

Hi @Anonymous ,

In your scenario, I would suggest you sharing the pbix file to us for analysis.

Best Regards,

Teige

[jdbuchanan71]

Do you have any other filters applie to the visual maybe?

[Anonymous]

No, except the ones already described above!

I am just retesting each of my measures (with If ) separtely using the VAR function and something really weird happens:

The moment I use || in the formula as mentioned above in my first post, the measure doesn't work  (the visual appears but not functiong correct!), so as you mentioned I do:

VAR A = IF ( NOT ISNUMBER ( [Actual] ), 0, [Actual])
VAR B = IF (NOT ISNUMBER ([TARGET], 0, [TARGET])

Alert = If (  [VAR A] < 1.15 * [VAR B]  ||  [VAR B] <> 0 , 1, BLANK() ) 

it doesn't work! but as I mentioned each of the measures separately work perfectly. So I conclude that there is problem with using "||" in my formula. What is the issue really then?



@MFelix 

Thanks very much to look at this and let me have your comments!

[jdbuchanan71]

If you change the measure

Alert = If (  [VAR A] < 1.15 * [VAR B]  ||  [VAR B] <> 0 , 1, BLANK() ) 

to

Alert = If (  [VAR A] < 1.15 * [VAR B]  ||  [VAR B] <> 0 , 1, 0 ) 

Does it work?  I wonder if there is something with the visual not liking a BLANK()

[Anonymous]

thanks @jdbuchanan71 ,

Yes already tried but it returns the same error!

[MFelix]

Hi @Anonymous ,

As I refer on my test with a dummy database the result was ok.

Can you share some more information about your data?

Regards,

MFelix

[Anonymous]

@MFelix  Thanks for your reply,

All the items in the measure you see are measures which calculated on the tables each. They all are Numeric values.

Does it enough to help?

[Anonymous]

@MFelixThanks

Something I don’t understand is how “||” works in the measure :

Alert = if ( [actual] < 1.15 * [target] || [target] + [actual] <> 0, 1, Blank())

I mean what is checked first and what then in the formula? According to my knowledge it will first tests [actual] < 1.15 * [target] and then [target] + [actual] <>0, correct?

So in this case if  [actual] > 1.15 * [target] then it is absolutely [target] + [actual] <> 0 and the blank never triggers!, right?

[MFelix]

Hi @Anonymous ,

Just in case try this measure:

Alert = if (OR( [actual] < 1.15 * [target] , [target] + [actual] <> 0) , 1, Blank())

Regards,

MFelix

[Anonymous]

HI,

It is not working! Actually it is exactly doing the same job as what I written in my first thread post.

[jdbuchanan71]

@Anonymous Can you show me the result of my suggestion from yesterday?

[Anonymous]

@jdbuchanan71  thanks,

The  same thing, no difference in result than what I posted!

Thanks

[jdbuchanan71]

@Anonymous 

Try this for me if you would.  Create two alert testing measures:

AlertTest1 = IF ( [actual] < 1.15 * [target], 1, Blank() )

AlertTest2 = IF ( [target] + [actual] <> 0, 1, Blank() )

Then on a blank page add a table with one of the items you are slicing on (department, year, category, whatever) and add those two measure plus your alert measure to the table just to see what the output is.  

You can add additional filtering items to the table, the point is to see what the output of the meaures is.

[MFelix]

Hi @Anonymous,

Is this the same dashboard as you add in previous posts?

I can try and check it with the mockup data I provided in another answer to you.

Regards
MFelix

[jdbuchanan71]

Hi @Anonymous 

I'm afraid at this point I am out of ideas.  I am going to need to see a sample of the model with the error in it.  Can you anonymize the data enough to share it with me and @MFelix  so we can see all the relationships and measures along with the error?

[MFelix]

Hi @Anonymous ,

The tests I have made all should work as expected, can you share part of the sample data through private message?

Regards,

MFelix

[Anonymous]

@MFelix  Thanks,

currenlty my problem seems to raise from the OR"||" condition usage, since I have tried to put all the formatting to Whole numbers using VALUE function  and checking each the Modeling tab to make sure all is set to number values.

Do you confirm the logic in the formula:

Alert = if ( [actual] < 1.15 * [target] || [target] + [actual] <> 0, 1, Blank())

Thanks

[jdbuchanan71]

What about if you change it to be nested IFs?

=
IF (
    [actual] < 1.15 * [target], 1,
    IF ( [target] + [actual] <> 0, 1, BLANK () )
)

[Anonymous]

Thanks @jdbuchanan71 ,

 I did try this formula before myself and it triggered the following error:

See the screenshots please:

As you see the visual should now be disapeared but instead it gives the "Could load"error!

Why this happens? I can't figure!

@MFelix 

@jdbuchanan71 

[jdbuchanan71]

That error looks like the problem is in an earlier step, one of the measures is retruning and empty string ("") and then you are trying to compare that to a number which is causing the error perhaps.

Do you have something in one of the earlier measures like IF(ISBLANK([result]),"",1) or IF A = BLANK(), "", A?