Adding a Text value in a date filter

[Malsk1_1]

Hi,

I have 2 tables

Table 1		Table 2
Baseline Date		Baseline Date
01/01/2021		13/10/2021
01/02/2021
01/03/2021
01/04/2021
01/05/2021
01/06/2021
01/07/2021
01/08/2021

I have merged the tables and therefore have a complete list of dates covering them both, however when i create a date picker filter for the merged dataset, i would like to show all dates from the combined list, however for the date that has come from Table 2 I would like to show it with 13/10/2021 Flash - as below:

Date Picker

01/01/2021

01/02/2021

01/03/2021

01/04/2021

01/05/2021

01/06/2021

01/07/2021

01/08/2021

13/10/2021 (Flash)

How can i do this?

[Pragati11]

Hi @Malsk1_1 ,

I am assuming your new Date column with merged dates is of TEXT datatype.

In that case just create a slicer, move this new Date column and select dropdown type on the slicer.

Share some screenshot here, as it can help us what you are seeing at your end? 🙂

Thanks,

Pragati

[Malsk1_1]

@Pragati11  Both are Date columns but in the date picker i am assuming they would need to be in text due to the text at the end of the date from table 2.

[Malsk1_1]

@Pragati11 the date filter should display like this :

[Pragati11]

Hi @Malsk1_1 ,

Sorry I didn't understand. 

Is the slicer not displaying values as in the screenshot? You already have column with all the values right?

Are you having issues with displaying them in an order? If yes, then do one thing.

Go to Power Query Editor and add an Index column to your table. Something like below:

Then apply your changes. Go to Data tab in Power BI and sort your textDate column by Index column:

Once you do that, you will get following:

Now create the slicer and you will get the way you want it:

Let me know if this works.

Thanks,

Pragati

[Pragati11]

HI @Malsk1_1 ,

Yes your columns need to be text type now as one of the values have a text with date value.

Thanks,

Pragati

[Malsk1_1]

@Pragati11 @I am assuming that I need to create a calculated field to say something like this.

if max date from table 2 does not exist in table 1, then show the max date in table 2 & Flash else show the dates from table 1.

how can I write this?

[Pragati11 · Accepted solution]

Hi @Malsk1_1 ,

So you don't have a date column with all those values.

Your query says, you have merged the tables and got the dates in place, so I got confused.

Suppose you have following 2 tables:

Table1

Table2

Make sure datatype of both Date columns in both tables is set to TEXT.

In Power Query editor, click on Table2 and add a custom column:

Then add following formula:

You will get the following:

Now I will remove previous column from Table2 and just keep the new caluclated column as follows:

Now in both Table1 and Table2, I am giving name to the columns as Date as this will help me in next steps and their datatype as Text.

In Power Query editor, you will have to append them together. Make sure the column name is same in both the tables.

Click on Table1 on the left and on the top ribbon select Append Queries as shown:

Then the following window:

You end up the required output:

 Now add an index column to this table, to make sure your slicer displays data in right order:

Then apply your changes. Go to Data tab in Power BI and sort your Date column by Index column:

You will end up with slicer in the required format:

Thanks,

Pragati