Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

IF STATMENT SHANIGANS

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

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.
     

6 Replies

  • Gabriel_Walkman's avatar
    Gabriel_Walkman
    Icon for Continued Contributor rankContinued Contributor
    5 years ago

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

     

    Also, please refrain from topics in all capital 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Yeah TestResultCount does. 

      Sure thing.

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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. 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.