I want to sort the rows and columns of the heatmap

[huseyincenik]

I'm trying to create a heatmap with Matrix Chart. I want to sort the rows and columns of the heatmap as DESC. By default, PowerBI only allows rows to be sorted. I wrote a DAX formula to sort my columns DESC. However, the code I wrote in dax does not filter. It returns a one-time value, but my values do not change afterwards. Can you help me with this?

My Dax Code:

Table 2 = 
// VAR ParamValue = SELECTEDVALUE(Parameter[Parameter])// now it is 6.
VAR ParamValue = 9
// VAR NumberTable = GENERATESERIES(1, ParamValue)
VAR RankedTable = 
    ADDCOLUMNS(
        TOPN(
            ParamValue + 1,
            SUMMARIZE(
                FILTER(
                    output,
                    NOT ISBLANK(output[Cleaned AI Industries])
                ),
                output[Cleaned AI Industries],
                "Total Summarized Relevance Score-1", [Summarized Relevance Score-1]
            ),
            [Total Summarized Relevance Score-1],
            DESC
        ),
        "Rank", RANKX(TOPN(
                            ParamValue + 1,
                            SUMMARIZE(
                                FILTER(
                                    output,
                                    NOT ISBLANK(output[Cleaned AI Industries])
                                ),
                                output[Cleaned AI Industries],
                                "Total Summarized Relevance Score-1", [Summarized Relevance Score-1]
                            ),
                            [Total Summarized Relevance Score-1],
                            DESC
                        ), [Total Summarized Relevance Score-1], , DESC)
    )
RETURN
    RankedTable

[Anonymous]

Hi @huseyincenik

As you know, sorting columns in a matrix or heat map is not directly supported in Power BI.

You can try to create a calculated column that ranks the values in descending order and then use this calculated column to sort the rows.

I see that you have already done this, but you seem to be experiencing some problems with your dax, here are the changes I have made to your dax.

Table_2 = 
VAR ParamValue = 9
VAR RankedTable = 
    SUMMARIZE(
        FILTER(
            output,
            NOT ISBLANK(output[Cleaned AI Industries])
        ),
        output[Cleaned AI Industries],
        "Total Summarized Relevance Score-1", [Summarized Relevance Score-1]
    ),
    "Rank", RANKX(SUMMARIZE(
                        FILTER(
                            output,
                            NOT ISBLANK(output[Cleaned AI Industries])
                        ),
                        output[Cleaned AI Industries],
                        "Total Summarized Relevance Score-1", [Summarized Relevance Score-1]
                    ), [Total Summarized Relevance Score-1], , DESC)
)

RETURN
    RankedTable

This code will create a table that includes your relevant columns and a new “Rank” column that ranks the industries in descending order of the “Total Summarized Relevance Score-1”.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[huseyincenik]

@Anonymous Hi, first of all, thank you for interest.

I did what you said, but I couldn't get the result I wanted. As I mentioned before, the filtering part of the code I wrote does not work. I filter the data outside, but this code does not update because it is not affected by the filter.

Table 2 = 
// VAR ParamValue = SELECTEDVALUE(Parameter[Parameter])// now it is 6.
VAR ParamValue = 9
// VAR NumberTable = GENERATESERIES(1, ParamValue)
VAR RankedTable = 
    ADDCOLUMNS(
        TOPN(
            ParamValue + 1,
            SUMMARIZE(
                FILTER(
                    output,
                    NOT ISBLANK(output[Cleaned AI Industries])
                ),
                output[Cleaned AI Industries],
                "Total Summarized Relevance Score-1", [Summarized Relevance Score-1]
            ),
            [Total Summarized Relevance Score-1],
            DESC
        ),
        "Rank", RANKX(TOPN(
                            ParamValue + 1,
                            SUMMARIZE(
                                FILTER(
                                    output,
                                    NOT ISBLANK(output[Cleaned AI Industries])
                                ),
                                output[Cleaned AI Industries],
                                "Total Summarized Relevance Score-1", [Summarized Relevance Score-1]
                            ),
                            [Total Summarized Relevance Score-1],
                            DESC
                        ), [Total Summarized Relevance Score-1], , DESC)
    )
RETURN
    RankedTable

While the back chart works with PowerBI's default, the front chart works with my code. While the first photo works without a filter, I apply a filter to the second photo. I am writing DAX code to sort columns by total. If you pay attention, since the DAX code is not filtered, it still shows the first output it showed when the filter is applied.