Forum Discussion

tbobolz's avatar
tbobolz
Resolver I
6 years ago
Solved

Simple IF statement not working. Help please!!!

Hi All,

 

I have used this system to auto find the current fiscal year many times in the past; however, today I just can't figure out why it is not working! The column in the below pbix file, "IsMaxCFY", seems correct but it returns true (or "1") for everything. I think I have clean fields, proper formatting. Am I just too tired tonight? 

 

https://drive.google.com/open?id=1FaCac9Ll6HObZ9z3Qma-K11M8b_rW3zK

 

Thanks for any help you can offer.

 

Terry

 

Need Help

6 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    6 years ago

    DAX is a challenging language to learn.  It seems straight forward but it is like an onion - the more you peel back the layers, the more you find new layers.

     

    Here is the formula you seek

    IsMaxCFY = if('Registration - Export'[Fiscal Year]=MAX('Registration - Export'[Fiscal Year]),1,0)
     
    The issue is complex and has to do with context transition.

     

    There are lots of things I would recommend you do differently.  Perhaps work through the knowledge base on my website

    https://exceleratorbi.com.au/knowledge-base/

     

    Be sure to read the best practices https://exceleratorbi.com.au/best-practices-power-pivot-power-query-power-bi/

     

     
    • tbobolz's avatar
      tbobolz
      Resolver I
      6 years ago

      Thanks Matt,

       

      Yes, such a simplier solution, I appreciatet your time and I WILL read through knowledgebase to grow. 

       

      Terry 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    In a calculated column formula (IsMaxCFY), you are referring to a measure (CFY).  Why are you doing so?  Please describe your question and show the expected result.

    • tbobolz's avatar
      tbobolz
      Resolver I
      6 years ago

      Hi Ashish,

       

      Thanks for the reply.  Measure CFY is meant to find the MAX year, which it does fine in the dataset. (2019)

       

      Then column IsMaxCFY should compare CFY to my fiscal years field with an IF statement and return a "1" if it is 2019, and "0" if the year is anything other. 

       

      I then use IsMaxCFY as a filter to set all dashboard elements to the current fiscal year, and with each new year, this will automatically update. This has worked in many other senarios, but for some reason, this thime it is not working.  I see Matt A has offered a solution too, that I have not yet applied. (Thanks Matt). But I am still curious why this method has failed me this time.

       

      In addtional, I'll use the same method to find the previous year, current/previous month to build YOY YTD comparison that once I connect to a gateway file, everything is auto set to update and correct its self as the years and month change.

       

      Thanks for you help.

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        6 years ago

        Hi,

        Why not just drag the FY to the Report filter section and select 2019.  All visuals across all pages would be filtered by 2019.