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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JBGyro
Regular Visitor

Possible to insert text in date column?

I have a date column that shows completed dates for projects, this is a data group to based off the actual date column so we can get the MMMM YYYY format. The blanks in this column represent projects that are still active. I'm looking to change the null values to read "Active" for the sake of clarity when using this column as a slicer. I've tried replacing values in Power Query and various DAX formulas but keep getting an error about not being able to compare date and text values. Is there a workaround to format the date to text and insert "Active" where null?

 

Sample DAX I've tried:

Column = IF([Finish Date (bins)] = "", "Active", FORMAT([Finish Date (bins)], "MMMM YYYY"))

 

Slicer I'm trying to update:

JBGyro_0-1712946439808.png

 

6 REPLIES 6
mahenkj2
Solution Sage
Solution Sage

Hi @JBGyro ,

 

I would suggest to use a simpler technique in such case.

 

Keep this column intact as Date column, instead creat a custom column say 'Status' which check if Completion date column have date then say 'Completed' else 'Not completed'.

 

This keeps things simple, date column remain Date column, use Status column in report as a slicer, legend etc, now u can connect completion date column to a standard Date table as well.

 

I think such a technique is cohesive to easier reporting.

 

Hope it helps.

Thank you for the reply. I'm actually already doing this, but some confusion arises when the user wants to see active and completed projects, but then doesn't select the (Blank) option along with specific 'Completed' months. 

lbendlin
Super User
Super User

BLANK() is not the same as ""

Thank you for the reply. This helped as I changed my formula to:

 

Column = IF(ISBLANK([Finish Date]), "Active", FORMAT([Finish Date], "MMMM YYYY"))

 

This changes the column to Text, but now it is sorted alphabetically so there is more work to be done. 

JBGyro_0-1713198061599.png

 

Hi @JBGyro ,

 

Incase you use a date table and use month filter from that, this blank will not appear in the slicer. and together with follow the my earlier input, your user should be fine. Date is a dimension and should preferably come from dimension table and not the fact table. A proper date table will add lot of flexibility in your report.

 

Hope it helps.

Hi, 

to make it sort order change use index table and relate with your custom column.
Ex of index table

Index       MonthYear
1              Active
2              Jan 2023
3              Feb 2023
4              Mar 2023
etc.
This dim table MonthYear column sort order should change by Index. I hope you know this concept.

Happy learning. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors