IF STATMENT SHANIGANS

[Anonymous]

Hi there, first time poster, im hoping someone can tell me whats going on here.

Ok so in my project i am using a measurment with this code 

========================================

var TestResultCount = COUNTROWS(TestConducted)
var FailRowCount = (COUNTROWS(aTable))
return

IF(TestResultCount >0,if(FailRowCount < 1,"Yes","No"),"NA")
========================================
so the output im expecting is
If there are tests, return yes, or No if the test succeeded of not. Or Return NA if there are no tests.

In the specific data set i am using there are no Fail cases, and 50% success and 50% NA

the issue i have is the above logic runs without error returning 100% NA

However if the line is changed to 
IF(TestResultCount >0,if(True()< 1,"Yes","No"),"NA")
i get my expected results with 50% of the results returning "Yes" and 50% returning "NA"

I believe this is because aTable is blank, however using Isblank(aTable ) 
EG. IF(TestResultCount >0,if(Isblank(aTable ),"Yes","No"),"NA")
Also doesnt work. with all results still returning NA.

I believe that when the success result of the first if statement hits an error, it instead of stopping just goes to NA. 

Please let me know your thoughts and opinions on why this is happening. 
Below is the full code
=======================================================

Test_Flow status Minus Below peak level =

var TestConducted = FILTER('Hourly Telem Data',

AND('Hourly Telem Data'[Flow status Minus Below peak level] <> "",

'Hourly Telem Data'[SiteRef] == [SiteRef]

))

var aTable = FILTER(TestConducted,

AND('Hourly Telem Data'[Flow status Minus Below peak level] == "Fail",

'Hourly Telem Data'[SiteRef] == [SiteRef]

))

var TestResultCount = COUNTROWS(TestConducted)

var FailRowCount = COUNTROWS(aTable)

return

if (TestResultCount >0,

if(FailRowCount < 1,"Yes","No"

),"NA")

========================================================

[Anonymous · Accepted solution]

Ok the issue is solved. 

Test_Flow status Minus Below peak level =

Changing 

VAR aTable =

FILTER (

TestConducted ,

AND (

'Hourly Telem Data'[Flow status Minus Below peak level] == "Fail",

'Hourly Telem Data'[SiteRef] = [SiteRef]

)

)

TO

VAR aTable =

FILTER (

'Hourly Telem Data',

AND (

'Hourly Telem Data'[Flow status Minus Below peak level] == "Fail",

'Hourly Telem Data'[SiteRef] = [SiteRef]

)

)

i think this may have something to do with how filtering sub tables work inside the if statment. All variables have their expected results outside the if statment, but when they are both used, the second variable is set to blank.

If you have an issue like this in future try to not filter a sub table if possible draw from the original table.

[amitchandak]

@Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[AlB]

Hi @Anonymous 

Is this a measure or a calculated column ? If the former, how/where are you using it?

What is this? What are you trying to do?

       'Hourly Telem Data'[SiteRef] == [SiteRef]

It would help if you show a sample of the relevant tables

Try this for the time being:

Test_Flow status Minus Below peak level =
VAR TestConducted =
    FILTER (
        'Hourly Telem Data',
        AND (
            'Hourly Telem Data'[Flow status Minus Below peak level] <> "",
            'Hourly Telem Data'[SiteRef] = EARLIER ( 'Hourly Telem Data'[SiteRef] )
        )
    )
VAR aTable =
    FILTER (
        TestConducted,
        AND (
            'Hourly Telem Data'[Flow status Minus Below peak level] == "Fail",
            'Hourly Telem Data'[SiteRef] = EARLIER ( 'Hourly Telem Data'[SiteRef] )
        )
    )
VAR TestResultCount =
    COUNTROWS ( TestConducted )
VAR FailRowCount =
    COUNTROWS ( aTable )
RETURN
    IF ( TestResultCount > 0, IF ( FailRowCount < 1, "Yes", "No" ), "NA" )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

the code i have constructed is for a measure.

The measure is meant to look over the "Flow status minus below peak level" field in the "Hourly Telem Data" .If the field is not entirely blank, state wheather or not there is one or more "Fail"s in that data set

The measure is set in a different table than "Hourly Telem Data", one which has a unque set of SIte refs, as well as all the other test Measures i need.

'Hourly Telem Data'[SiteRef] == [SiteRef]
This is used to connect the hourly Telem data set to the current tables SiteRef Key

as there is a one to many relationship between the current table and the Hourly Telem Data table.

I tried your solution, power bi wouldn't except the use of the Earlier function stating incorrect paramater type. 

[Gabriel_Walkman]

@Anonymous if you test just your TestResultCount, does it produce the correct number?

Also, please refrain from topics in all capital 🙂

[Anonymous]

Yeah TestResultCount does. 

Sure thing.