Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Quick DAX question

  • 1.  Quick DAX question

    Bronze Contributor
    Posted Jun 12, 2019 01:17 PM
    ​I have a column in my query "Est. Year of Completion" - this column is formatted as Whole Number (123)
    I need to create new column.
    Do I do it via "Add Column" > "Custom Column"?

    I need to return in new column:
    If year is CURRENT YEAR or older then A otherwise B

    This is what I was trying to produce:

    if (YEAR([Est. Year of Completion] <=YEAR(TODAY()) then "A" else "B"))

    but I'm getting error: Token Comma expected.

    Can someone help, please?


    ------------------------------
    Michael Michniadowicz
    2102078768
    ------------------------------


  • 2.  RE: Quick DAX question

    Gold Contributor
    Posted Jun 12, 2019 01:20 PM
    You're missing a ")" after [Est. Year of Completion]...

    ------------------------------
    Danny Dennison, MCSA
    http://bit.ly/MCSA_BI
    Business Intelligence Analyst
    Reynolds Lake Oconee
    Greensboro, GA
    7064671624
    ------------------------------



  • 3.  RE: Quick DAX question

    Bronze Contributor
    Posted Jun 12, 2019 02:27 PM
    Edited by Michael Michniadowicz Jun 12, 2019 02:34 PM
      |   view attached
    I've changed it to:

    if (YEAR([Est. Year of Completion]) <=YEAR(TODAY()) then "A" else "B"))

    Getting error: "Token RightParen" expected.





    ------------------------------
    Michael Michniadowicz
    2102078768
    ------------------------------



  • 4.  RE: Quick DAX question

    Bronze Contributor
    Posted Jun 12, 2019 02:51 PM
    OK, I figured it out, I had to do it under Modeling in the main window, not ​in Query Editor.

    A or B = if (YEAR([Est. Year of Completion]) <=YEAR(TODAY()), "A","B")

    Code works … BUT
    I have Est. Year of Completion years of 2015,2016,2017,2018,2019,2020,2021,2021
    And the code populates ALL rows with A. And the ones in the future should be B, no?

    ------------------------------
    Michael Michniadowicz
    2102078768
    ------------------------------



  • 5.  RE: Quick DAX question

    Bronze Contributor
    Posted Jun 12, 2019 03:10 PM
    OK!!!!

    It works!!!

    Under Modeling, this code:

    A or B = if (([Est. Year of Completion]) <=YEAR(TODAY()), "A","B")​

    ------------------------------
    Michael Michniadowicz
    2102078768
    ------------------------------



  • 6.  RE: Quick DAX question

    Top Contributor
    Posted Jun 12, 2019 02:55 PM
    Edited by William Rodriguez Jun 12, 2019 02:56 PM

    Hi @Michael Michniadowicz: ​

    Kindly, you do not need a "THEN" or "ELSE" statement within a DAX "IF" statement. 

    Try the following: IF( YEAR( [Est. Year of Completion] ) <= YEAR( TODAY() ) , "A" , "B" )

    Hope this helps!
    William



    ------------------------------
    William Rodriguez
    Business Analyst | MCSA: BI Reporting
    ------------------------------



  • 7.  RE: Quick DAX question

    Bronze Contributor
    Posted Jun 12, 2019 03:11 PM
    Exactly, and I've removed the first YEAR (the one after IF)

    Thank you all! :-)​

    ------------------------------
    Michael Michniadowicz
    2102078768
    ------------------------------