IFS in Excel to PowerBI with text strings

[Anonymous]

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

[V-lianl-msft]

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.

[Anonymous]

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?

[harshnathani]

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)

[amitchandak]

@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]

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

[V-lianl-msft]

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.

[nandukrishnavs]

@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 🙂

[Fowmy]

@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

[amitchandak]

@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?

[harshnathani]

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)

[Anonymous]

Unfortunately, the code doesn't work.

It replys a wrong syntax