Slicer - need to have greater-than-or-equal-to ability on a text value

[RandyShroyer]

I know that Power BI doesn't allow a "slider" on a text slicer, but I was wondering if anyone has developed a work around?

We have a graph with the X access being fiscal months.  The goal is to give the user the ability to to choose the starting fiscal month for the graph.  The users are not going to know the exact starting date of a fiscal month so using the date in the slider isn't ideal.  Instead, we would like the user to see a text representation in the slicer ("FY2020-01", "FY2020-02", etc.) but we still need the "on or after" capability inherent in the slicer when a date or measure is the slicer value.  I did find a clever solution on-line involving a second date table that filters the main date table, but to apply it to our data would create a huge table that would become unmanageable.

Has anyone been able to conquer this problem?

[Anonymous · Accepted solution]

Hi @RandyShroyer,

Current power bi filters do not support applying 'greater than or equal to' mode on text fields.
I'd like to suggest you extract these values to create an unconnected table as the source of the slicer. Then you can create a measure formula to convert them to numeric values and compare them with current values and use it as a filter on 'visual level filter' to filter records.

formula =
VAR selected =
    VALUE (
        RIGHT ( SUBSTITUTE ( SELECTEDVALUE ( NewTable[Fiscal Date] ), "-", "" ), 6 )
    )
VAR _currValue =
    VALUE (
        RIGHT ( SUBSTITUTE ( SELECTEDVALUE ( Table[Fiscal Date] ), "-", "" ), 6 )
    )
RETURN
    IF ( _currValue >= selected, "Y", "N" )

Applying a measure filter in Power BI - SQLBI

Regards,

Xiaoxin Sheng

[RandyShroyer]

Thank you!

[RandyShroyer]

Thanks for the interest!

Yes, your suggestion works for when they want to start at the first month of the FISCAL YEAR.  But, they also might want to start at the second or third month (FY2020-02, FY2020-03).  I can create a measure that is 202002 or 202003, but even if the user understands that, the sequence in the slider would be 202001, 202002, 202003, 202004, 202005, 202006, 202007, 202008, 202009, 202010, 202011, 202012, 202013, 202014, 202015, 202016,…    The bolded values make no sense to the user.

[amitchandak]

@RandyShroyer , Create FY Year Out if it and then you can use it in slicer

A new column

FY Year = mid([FY],3,4)  // FY2020-01 will give 2020