Forum Discussion
Sort multiple tables using dax
Create a Calculated Column: Go to your data model and create a new calculated column. You can do this by selecting the table, then under the "Modeling" tab, click on "New Column." Use the following DAX formula to create a combined column:
CombinedColumn =
IF (
SELECTEDVALUE ( 'YourButtonsTable'[SelectedButton] ) = "Started at",
'YourTable'[LearnerID] & 'YourTable'[Started at],
'YourTable'[LearnerID] & 'YourTable'[Completed at]
)
Replace 'YourButtonsTable' with the actual name of your buttons table, and 'YourTable' with the actual name of your data table.
Sort the Table: After creating the calculated column, go to your table visualization and add the new calculated column ('CombinedColumn') to the sorting section of the table. This will sort the table first by Learner ID and then by 'Started at' or 'Completed at' based on the selected button.
Set Default Sorting: Set the default sorting for the 'CombinedColumn' based on the 'Started at' or 'Completed at' button selected. You can use the following DAX formula for that:
DefaultSortColumn =
IF (
SELECTEDVALUE ( 'YourButtonsTable'[SelectedButton] ) = "Started at",
'YourTable'[Started at],
'YourTable'[Completed at]
)
Then, go to the "Modeling" tab, select the 'CombinedColumn', and set the default sort order based on the 'DefaultSortColumn'.
Now, when you switch between 'Started at' and 'Completed at' using your buttons, the table should be sorted first by Learner ID and then by the respective date column. Make sure to adjust the table and column names according to your actual data model.
I don't have a button table just three separate buttons
If you have three separate buttons and not a dedicated table, you can still achieve the dynamic sorting using a DAX measure. Assuming you have individual slicers or buttons for "Registered at," "Started at," and "Completed at," you can create a DAX measure to dynamically determine the sorting order. Here's how you can modify the measure:
SortOrder =
SWITCH(
TRUE(),
VALUES('YourTable'[Registered at]), [Learner ID],
VALUES('YourTable'[Started at]), [Started at],
VALUES('YourTable'[Completed at]), [Completed at]
)In this example, replace 'YourTable' with the actual name of your table. This measure works by checking which column is in the filter context based on the selected button and then uses that column for sorting.
Follow the steps from the previous response to create a SortColumn in your table and use it for sorting in the table visualization. The sorting will dynamically switch between columns based on the selected button.
This approach assumes that only one button is selected at a time. If you have multiple buttons selected simultaneously, you may need to adjust the logic accordingly.