Sort date field in Matrix column header descending

[ns29]

I have already seen the following:

https://www.excelguru.ca/blog/2018/06/14/ranking-method-choices-in-power-query/

https://stackoverflow.com/questions/49685042/powerbi-sort-columns-in-matrix-visual

I am having a hard time understanding the solution. Honestly, I'm just looking for a pivot table kind of solution. Please attached screenshot. 

Thanks!

[v-lid-msft]

Hi @ns29 ,

We can create two calculated column, then use ShowDate Column to sort by rank column and use ShowDate Column as column header to meet your requirement:

Calculated Columns:

ShowDate = [Date]

Rank = RANKX('Table',[Date],,,Dense)

Sort by:

Matrix visual:

Best regards,

[ns29]

@v-lid-msft thanks for your response. Seems like the proposed solution is similar to other solutions I've seen. Baffles me that something so apparently simple requires such a complicated workaround. But there are probably good reasons why the product is designed this way which my simple mind may not be able to comprehend. I'll give it a shot and report back.

PS: I get the Rank field but what is the point of the ShowDate field since it appears to be identical to the Date field. I'm trying to understand the mechanics rather than blindly apply the solution. Thanks again!

[amitchandak]

Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[ns29]

@amitchandak  Thank you for the quick response! Happy to send over samples but have a super rookie question - how do I attach anything other than images on here? So embarrased to ask...

[amitchandak]

You can upload to dropbox, onedrive and share

[ns29]

@amitchandak thanks, see link below for samples.

I have uploaded both an Excel file and a pbix file. Basically, the Excel has a pivot table that I'm trying to mimic in Power BI. Here are the things I'm trying to do and not finding a solution or at least a simple way akin to Excel pivot tables - the first is about the original post but hoping to get a few more answers while I'm at it. 

• Sort date type column header in descending order
• I have 3 different values in the pivot table, I'd like to see them values first, date next in the pivot table. Meaning, I want to see all the numbers for my first value with corresponding dates, then the next value and so on. In the pivot table, it's a simple drag and drop, I have been totally unable to do that in Power BI. It forces me to see all 3 values for the first date, then the next date and so on.
• Please see measure %_of_sample in the pbix. In the pivot table, it's a simple step of adding the value a second time to the pivot and right clicking and choosing % of etc. I researched it and came up with this measure but was wondering if there was a simpler solution. 

That's all I can think of for the moment, thank you!

https://drive.google.com/drive/folders/1VY2yV7MKE-DHRpQcgl9e2U9Xd4YbYTxV?usp=sharing

[v-lid-msft · Accepted solution]

Hi @ns29 ,

We can use the following steps to meet your requirement:

1. create a calculated table using following DAX:

ColumnHeader = 
ADDCOLUMNS (
    CROSSJOIN (
        DATATABLE (
            "ValueHeader", STRING,"ValueRank",INTEGER,
            {
                { "%_of_sample",2 },
                { "metric_value_1" ,1},
                { "metric_value_2",3 }
            }
        ),
        DISTINCT ( 'Table1'[metric_month] )
    ),
    "Rank", RANKX (
        DISTINCT ( 'Table1'[metric_month] ),
        [metric_month],
        ,
        DESC,
        DENSE
    )
)

2. Make metric_month column sort by Rank column, then make ValueHeader sort by Rank column.

3. create relationship between two tables based on the ColumnHeader column

4. create a measure used in the value field of matrix visual:

ValueInMatrix = SWITCH(SELECTEDVALUE('ColumnHeader'[ValueHeader]),"metric_value_1",SUM('Table1'[metric_value_1])&"","metric_value_2",SUM('Table1'[metric_value_2])&"","%_of_sample",FORMAT([%_of_sample],"Percent"))

Best regards,

[ns29]

@v-lid-msft Thank you for the detailed response and the effort you put into providing the sample output. I'll play with it but it does seem like a lot of hoops to jump through to get to some seemingly simple results. I'll play with this and also evaluate against Tableau. But, definitely marking this as solved.

Thank you again!