Filter containing mixed types of data

[Anonymous]

I have a column that contains both dates and two text values.

I would like to create a filter using this column that would act like a normal "Date" filter. Now when I use this column, it is perceived as a text column and all the dates are sorted in text order, which makes it impossible to use.

Could you please provide an idea of how to present this column so it would act as a filter that it is convenient to use.

[Washivale]

Hi @Anonymous ,

You can create a new column from modeling menu with below dax function and use it in your filter

Column = IF(ISERROR(CONVERT('Table'[Column1],datetime)),blank(),CONVERT('Table'[Column1],datetime))

let me know if it helps.

Regards,

Washivale

[Anonymous]

@Washivale , thank you for the idea.

When I try to use the formula provided, this is what I get:

I am just trying to understand why I am receiving errors for the Data type?

[Washivale]

Hi @Anonymous ,

Somehow intellisense shows an error on the data type, I tried finding reason for error on datatype with no luck. However, when you hit enter after adding this function to new column, it does work as expected. 

Regards,

Washivale

[Anonymous]

@Washivale , in my case it did work. However, the text part of the column was converted into blanks, as they are errors. All the rest fields were converted into dates, as expected. 

Thus, by getting blanks, I cannot include the text fields into the filter that I would like to create as a visual.

[Washivale · Accepted solution]

HI @Anonymous ,

you would need to tackle this with multi slicer setup, since you have a new column in place. you can create one more column with similar function, however, you would replace the outcome as below

Column2 = IF(ISERROR(Convert('reporting-usage-rights'[Expiry date - Changed], DATETIME)),
'reporting-usage-rights'[Expiry date - Changed], "Date")

you can use this as your primary slicer, while the column you have created earlier will be your secondary slicer, on selection of Date from primary slicer your second slicer will show all the dates. 

Thank you,

Washivale