Power BI | Dynamic Sorting Based on User Selection

[AkashGhinmine]

Hi Everyone! 

I have a task in Power BI and needed your help on dynamic sorting. 

I have the below table in my power bi report - 

And I have created the below matrix visual to show details about Tender Forecasts.

I used the below DAX to create a Forecast Table first to unpivot the dates - 

Foreacst =

UNION(

    SELECTCOLUMNS(

        Tenders,

        "Tender Name", Tenders[Tender Name],

        "Tender Forecast Type", "ITT",

        "Forecast Date", Tenders[ITE Forecast Date]

    ),

    SELECTCOLUMNS(

        Tenders,

        "Tender Name", Tenders[Tender Name],

        "Tender Forecast Type", "PQQ",

        "Forecast Date", Tenders[PQQ Forecast Date]

    )

)

And created a measure to concatenate the Forecast Types - 

Concatenated Forecast Type = CONCATENATEX(Foreacst, Foreacst[Tender Forecast Type], " / ")

I wish to give the report users to have flexibility to select by which date field they want to sort the [Tender Name] by. The idea is to give a slicer or button in the report so that when the user selects the option (Sort By ITT or Sort by PQQ) the Tender Names are sorted by the selected field.

Do you think it is possible to achieve? If yes, could you please provide a solution or idea to approach this problem?

Note - I do not wish to add a new field in the table visual.

Thanks,

Akash

[Anonymous]

Hi @AkashGhinmine 

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.

[AkashGhinmine]

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

[Anonymous · Accepted solution]

Hi @AkashGhinmine 

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.

[mh2587]

You can achieve this using bookmarks and button combination

[AkashGhinmine]

Hey @mh2587 , 

Apologies for my mistake, I gave the wrong visual Screenshot. Unfortunately, I don't think bookmarks would work in this case becausse those date fields will not be a part of the visual. The idea would be to sort specific column [Tender Name] by the dates dynamically. I assume bookmarks would work only when we have those fields present in the visual.

Thanks,

Akash

[NehaIngale_1111]

I am stuck with similar situation, can anyone please provide solution