Forum Discussion

RMRaptor's avatar
RMRaptor
Frequent Visitor
3 years ago
Solved

Custom Date Sort

Hello All How to get this custom sort applied in tale matrix  on Date column  when  date vaue is TBD, display project at the end of rows or as last project . Project,Date column is dynamic feed from...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi RMRaptor,

    You can create a new table with corresponding date field values in text format and custom column with index based on date values to use on 'sort by column' property. (use if statement to check if current value equal to TBD and return a larger number to keep its order to bottom)

    Custom Sorting in Power BI 

     

    SortTable =
ADDCOLUMNS (
    VALUES ( 'Sample'[Date] ),
    "Index",
        IF (
            [Date] = "TBD",
            COUNTROWS ( 'Sample' ),
            COUNTROWS (
                FILTER (
                    'Sample',
                    [Date] <> "TBD"
                        && DATEVALUE ( [Date] ) <= DATEVALUE ( EARLIER ( 'Sample'[Date] ) )
                )
            )
        )
)

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi RMRaptor,

You can create a new table with corresponding date field values in text format and custom column with index based on date values to use on 'sort by column' property. (use if statement to check if current value equal to TBD and return a larger number to keep its order to bottom)

Custom Sorting in Power BI 

 

SortTable =
ADDCOLUMNS (
    VALUES ( 'Sample'[Date] ),
    "Index",
        IF (
            [Date] = "TBD",
            COUNTROWS ( 'Sample' ),
            COUNTROWS (
                FILTER (
                    'Sample',
                    [Date] <> "TBD"
                        && DATEVALUE ( [Date] ) <= DATEVALUE ( EARLIER ( 'Sample'[Date] ) )
                )
            )
        )
)

 

Regards,

Xiaoxin Sheng