Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

If else then Otherwise in calculated column

Hello all,

 

 

i created a calculated column in my Date Table to realize this rules:


 

2018-01-19 15_22_44-Power BI – Did you know ALL Power BI Services Servers are in UTC_ Now how to han.png

 

This is my current Calculated Column formula:

 

Column =
IF('DATE'[Date] = 'DATE'[Yesterday], 'DATE'[Yesterday],
IF('DATE'[Date] = 'DATE'[Current Date], 'DATE'[Current Date],
THEN "Other Day")

)

 

 

But unfortunatly i get a error as return value on the Column.

 

 

Any suggestions? I 

1 ACCEPTED SOLUTION
v-huizhn-msft
Employee
Employee

Hi @Anonymous,

From your screenshot, you created a custom column in Query Editor rather than creating a calculated colunmn using DAX formula. And your date column should be Date type date, while you type the Yesterday and Current Date(they are text date), so it returns error because date equals text type(like 2018/1/22=Current Date).

I create a sample table. Today is 2018/1/22.

1.PNG

In Query Editor, I click the yesterday and today in Calendar(highlighted in red circle), it will return correct result.

add conditionadd conditionresultresult

If you want to create a calculated column using DAX instead of Query statement, you can create it using the formula.

Calculated column =
IF (
    Table1[Date] = TODAY (),
    "Current Date",
    IF (
        Table1[Date]
            = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) - 1 ),
        "Yesterday",
        "Other Day"
    )
)


Please see the result as follows.

6.PNG

Best Regards,
Angelia

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

I had the same case and I did it just like in excel and it worked. Since the output in this case is a string not a column then we can just add it like that. 

First "if" is first condition, 2nd "if" is else of the first if, then final else is empty 

Column = 

IF('DATE'[Date] = 'DATE'[Yesterday], "Yesterday", IF('DATE'[Date] = 'DATE'[Current Date], "Current Date", "")

v-huizhn-msft
Employee
Employee

Hi @Anonymous,

From your screenshot, you created a custom column in Query Editor rather than creating a calculated colunmn using DAX formula. And your date column should be Date type date, while you type the Yesterday and Current Date(they are text date), so it returns error because date equals text type(like 2018/1/22=Current Date).

I create a sample table. Today is 2018/1/22.

1.PNG

In Query Editor, I click the yesterday and today in Calendar(highlighted in red circle), it will return correct result.

add conditionadd conditionresultresult

If you want to create a calculated column using DAX instead of Query statement, you can create it using the formula.

Calculated column =
IF (
    Table1[Date] = TODAY (),
    "Current Date",
    IF (
        Table1[Date]
            = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) - 1 ),
        "Yesterday",
        "Other Day"
    )
)


Please see the result as follows.

6.PNG

Best Regards,
Angelia

Anonymous
Not applicable

Thank you so much Angelia !

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.