Forum Discussion
Power BI | Dynamic Sorting Based on User Selection
You can try to create two tables to store [Tender Name] and the rankings based on two different date columns, and use field parameters to switch the sorting.
1. Create two tables using the following DAX:
ITT_TenderRanks = ADDCOLUMNS( DISTINCT('Table'[Tender Name]), "ITT Rank", RANKX(ALL('Table'), CALCULATE(MAX('Table'[ITE Forecast Date])), , ASC, Dense) )PQQ_TenderRanks = ADDCOLUMNS( DISTINCT('Table'[Tender Name]), "PQQ Rank", RANKX(ALL('Table'), CALCULATE(MAX('Table'[PQQ Forecast Date])), , ASC, Dense) )2. Set the [Tender Name] column in both tables to be sorted by the ranked column:
3. Create a one-to-many unidirectional relationship between these two tables and the Forecast table:
4. Select the [Tender Name] field in both tables to create the field parameter:
5. Create a slicer with the field parameter and replace the [Tender Name] field with the field parameter in the matrix visual.
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please try the following steps:
1. Create a new table to store the sorting options
SortOptions = DATATABLE(
"Sort By", STRING,
{
{"Sort By ITT"},
{"Sort By PQQ"}
}
)2. Use the SortOptions[Sort By] field to create the slicer and check the "Single select" option in the slicer settings.
3. Create a measure that applies different date columns to be sorted based on the slicer selection.
SortOrder =
VAR ITT = MAX('Table'[ITE Forecast Date])
VAR PQQ = MAX('Table'[PQQ Forecast Date])
VAR Selection = SELECTEDVALUE(SortOptions[Sort By])
RETURN
IF(Selection = "Sort By ITT", ITT, PQQ)4. Create a table visual and drag Table[Tender Name] and the measure SortOrder into it.
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
hanks for your solution, but this does not work for the specific solution we are trying to build. I have updated the question to best explain what we wih to achieve. Could you please re-check the question and provide your inputs if we can achieve the same.
The problem here is that I wish to sort specific column Tender Name dynamically due to the matrix visual and I dont want to add dates in that matrix visual as we already have date in it.
Thanks,
Akash
You can try to create two tables to store [Tender Name] and the rankings based on two different date columns, and use field parameters to switch the sorting.
1. Create two tables using the following DAX:
ITT_TenderRanks = ADDCOLUMNS( DISTINCT('Table'[Tender Name]), "ITT Rank", RANKX(ALL('Table'), CALCULATE(MAX('Table'[ITE Forecast Date])), , ASC, Dense) )PQQ_TenderRanks = ADDCOLUMNS( DISTINCT('Table'[Tender Name]), "PQQ Rank", RANKX(ALL('Table'), CALCULATE(MAX('Table'[PQQ Forecast Date])), , ASC, Dense) )2. Set the [Tender Name] column in both tables to be sorted by the ranked column:
3. Create a one-to-many unidirectional relationship between these two tables and the Forecast table:
4. Select the [Tender Name] field in both tables to create the field parameter:
5. Create a slicer with the field parameter and replace the [Tender Name] field with the field parameter in the matrix visual.
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.