Sort multiple tables using dax

[Anonymous]

I have a table like this in the table format in power bi 

Name    Email.   Course   Registered at.    Started at.     Completed at

I also have three buttons like this 

So I can switch between Registered at , Started at and Completed at. Now when Started at is selected not only the date range changes to 'Started between', but a filter also gets applied to the table so it only shows rows where the 'Started at' is not blank. The same is the case for 'Completed at'. I am doing and handling all of this using bookmarks.

Now there is a new requirement. I need to sort the table first by Learner ID (which is not present in the table displayed but I do have it this filed in the table getting from the query) and then by 'Started at' when 'Started at' is selected and sort by 'Completed at' when 'Completed at' is selected. 

If I had the Learner ID in the table in the dashboard it would have been a straighforward task but now I have to (probably) use DAX to sort the table by Learner ID and then by 'Started at' when 'Started at' is selected. Very confused on how this can be achieved. Any help?

[v-zhangti]

Hi, @Anonymous 

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it. If this fails to resolve, can you provide more example data and desired output?

Best Regards

[123abc]

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]
)

1. Replace 'YourButtonsTable' with the actual name of your buttons table, and 'YourTable' with the actual name of your data table.

2. 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.

3. 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]
)

1. 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.

[Anonymous]

I don't have a button table just three separate buttons

[123abc]

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.