Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

IFS in Excel to PowerBI with text strings

Hey there,

 

I need to transform this formula out of excel into PowerBI:

=IFS(AB3="";"N/A";AB3<=(YEAR(TODAY()));"YES";AB3>(YEAR(TODAY()));"NO")

 

My idea is:

In Overhaul Window? RETROFITS = IFERROR(IF([Overhaul Window Start RETROFITS]<=YEAR(NOW()),"YES","NO"),"N/A")
Unfortunately, PowerBI doesn't give back the text string "N/A".
 
How can I give back simple textstrings, if a cell is empty?
It seems to be so easy, but I couldn't figure out how to give back textstrings (BLANK, UPPER, LOWER....)
 
 
Need Help

11 Replies

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

    Anonymous , Something like this.

    Switch(True(),

    isblank([AB3] , "No",

    [AB3] = "NA" , "No"

    [AB3] <= year(Today()) &"" ,"Yes",

    "No"

    )

     

    Can you share sample data and sample output in table format?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Unfortunately, the code doesn't work.

      It replys a wrong syntax

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI Anonymous ,

       

      Create a Calculated Column

       

      Column =
SWITCH (
    TRUE (),
    'Table'[AB3]
        = BLANK ()
        || 'Table'[AB3] = "", "N/A",
    'Table'[AB3]
        < YEAR (
            TODAY ()
        ), "YES",
    'Table'[AB3]
        > YEAR (
            TODAY ()
        ), "No"
)

       

       

       Regards,
      Harsh Nathani
      Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

    Try this as a new column:

     

    RETROFITS = 
 IF( 
    ISBLANK([Overhaul Window Start RETROFITS]),
    "N/A",
    IF(
        [Overhaul Window Start RETROFITS]<=YEAR(NOW()),
        "YES",
        "NO"
    )
 )

     

     

    ________________________

    Did I answer your question? Mark this post as a solution, this will help others!.

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube, LinkedIn

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

     

    Try below the DAX in the calculated column

    ColumnValue =
VAR __Year =
    IF ( ISERROR ( VALUE ( MyTable[Year] ) ), BLANK (), VALUE ( MyTable[Year] ) )
VAR __Result =
    SWITCH (
        TRUE (),
        ISBLANK ( __Year ), "No",
        __Year <= YEAR ( TODAY () ), "Yes",
        __Year > YEAR ( TODAY () ), "No"
    )
RETURN
    __Result



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

     

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    Format "Overhaul Window Start RETROFITS" as whole number.

    Try this calculated column:

    Column = SWITCH(TRUE(),
ISBLANK('Table'[Overhaul Window Start RETROFITS]),"n/a",
'Table'[Overhaul Window Start RETROFITS]>=YEAR(NOW()),"Yes",
'Table'[Overhaul Window Start RETROFITS]<YEAR(NOW()),"No")

    If "Overhaul Window Start RETROFITS" is a measure,try create this measure:

    Measure 2 = SWITCH(TRUE(),
ISBLANK([Overhaul Window Start RETROFITS]),"N/A",
[Overhaul Window Start RETROFITS]>=YEAR(NOW()),"Yes",
[Overhaul Window Start RETROFITS]<YEAR(NOW()),"No")

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello together,

     

    I use a SWITCH TRUE function und receive the error that I have different data formats (True/False and Text) and that I need to transform the data. Here is the code:

     

       
        SWITCH (
        TRUE (),
        Results[In Overhaul Window? RETROFITS] = "NO", "NO",
             Results[In Overhaul Window? RETROFITS] = "YES" && Results[Overhaul already performed? RETROFITS] = FALSE(), "YES",
                 Results[In Overhaul Window? RETROFITS] = "YES" && Results[Overhaul already performed? RETROFITS] = TRUE(), "YES",
                    Results[Last Service Event Year] + Results[Overhaul Criteria RETROFITS] >= YEAR(TODAY()), "NO","YES", "N/A"

     

    I tried to transform the True/False format in a string but it did'nt work.

    How can I transform it with VALUE or FORMAT or any other function so that both formats are the same?

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

      Anonymous , "N/A" is additional

       

      SWITCH (
      TRUE (),
      Results[In Overhaul Window? RETROFITS] = "NO", "NO",
      Results[In Overhaul Window? RETROFITS] = "YES" && Results[Overhaul already performed? RETROFITS] = FALSE(), "YES",
      Results[In Overhaul Window? RETROFITS] = "YES" && Results[Overhaul already performed? RETROFITS] = TRUE(), "YES",
      Results[Last Service Event Year] + Results[Overhaul Criteria RETROFITS] >= YEAR(TODAY()), "NO","YES")

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        this doesn't work too because of the formats....

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous ,

       

      Think this is the same follow up post.

       

       Share  sample data or screenshot removing all sensitive information

       

      Column =
SWITCH (
    TRUE (),
    Results[In Overhaul Window? RETROFITS] = "NO", "NO",
    Results[In Overhaul Window? RETROFITS] = "YES"
        && Results[Overhaul already performed? RETROFITS]
            = FALSE (), "YES",
    Results[In Overhaul Window? RETROFITS] = "YES"
        && Results[Overhaul already performed? RETROFITS]
            = TRUE (), "YES",
    Results[Last Service Event Year] + Results[Overhaul Criteria RETROFITS]
        >= YEAR (
            TODAY ()
        ), "NO",
    "YES"
)

       

      Regards,
      Harsh Nathani
      Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • V-lianl-msft's avatar
    V-lianl-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    If the problem persists,could you share the sample pbix or sample data?

    Please mask any sensitive data before uploading

     

    Best Regards,
    Liang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.