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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
AnetaK
Helper V
Helper V

IF on date - Expressions that yield variant data-type...

Hello,

 

I have four status dates and want to show on the report always the latest status with the possibility to change to other dates.

So I want to have "Last status" and the other dates.

Something like this: Column2 = IF('Status date'[Status date]=MAX('Status date'[Status date]);"Last status";'Status date'[Status date]).

But I get an error.

However it works for Column1 = IF('Status date'[Status date]=MAX('Status date'[Status date]);1;0)

5.PNG

 

Could anybody, please, help me with it?

 

 

1 ACCEPTED SOLUTION

Ha! I've found the solution! 🙂

 

Status date = If('Status date'[Status date_date]=maX('Status date'[Status date_date]);"Last status";format('Status date'[Status date_date];"YYYY-MM-DD"))

View solution in original post

6 REPLIES 6
BeemsC
Resolver III
Resolver III

Basically what that error is saying, is that you are trying to use a string, where an integer was expected.
So you are trying to write something, where a numeric value is expected. That's why 1 and 0 are working.

You either need to make both integer, or both strings.
So your code fixed would look something like this :

Column2 = IF('Status date'[Status date]=MAX('Status date'[Status date]);"Last status";"Not last status")

But I need a date.

I want to have a slicer with dates and "Last status".6.PNG

 

 

 

You can't have the dates and the 'Last Date' together in one column/slicer, (for as far as i know)

So what should I do?

I would like to set the slicer on "Last status" so when the data is refreshed and there are new statuses, the report always shows the newest status.

Yea, like i said. You were good on your way with the formula. But its not possible for both the dates and "Last date".
If you use something like :
Column2 = IF('Status date'[Status date]=MAX('Status date'[Status date]);"Last status";"Not last status")

Or alternatively, if you only want "Last status"
Column2 = IF('Status date'[Status date]=MAX('Status date'[Status date]);"Last status";"")

Then you just add column 2 to a slicer. And if your relationships are correct, it should work

Ha! I've found the solution! 🙂

 

Status date = If('Status date'[Status date_date]=maX('Status date'[Status date_date]);"Last status";format('Status date'[Status date_date];"YYYY-MM-DD"))

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.