matrix sort DATE rows while entire matrix is sorted by another column - how?

[orbs]

hello all, newbie here.

i'm creating a matrix visual, with one of the rows being DATE (without year/qtr/month/day hierarchy). the matrix is sorted using one of the columns, so naturally, the "rows" column (the left-most column, containing the dimenstions hierarchy) is not sorted.

now, when i expand the rows all the way into the DATE level (which may or may not be the last level), the dates are obviously not sorted, because the entire matrix is sorted by another column.

my wish is to have only the few rows of the DATE level sorted (in descending order, i.e. latest date first), but leave the rest of the matrix still sorted as it was. how would i accomplish that?

thanks in advance!

[amitchandak]

@orbs , Multiple sorting is not supported on matrix visual. You can log an idea :https://ideas.powerbi.com/ideas/

[tex628]

It's not supported, but you can work around it. 


Create a measure and return the Amount for the totals. And the dates in numeric for the individual dates. 

Sort by this column, turn off wordwrap for column headers, values and then hide the column:


Br,
J

[orbs]

thank you @tex628 , yes that is what i mean.

but how can i measure differently based on the row? for any row that is not DATE, i can simply return the value of the original sorting column; but how can i detect that the row is a DATE so i can return the numeric value of the date?

[tex628]

The measure I used in my example is the following:

Measure 2 = IF( SELECTEDVALUE('Table (2)'[Date]) = BLANK() , SUM('Table (2)'[Amount]) , SELECTEDVALUE('Table (2)'[Date]))

Can you apply this to your model? 

/ J

[orbs]

ok, in my case the "Amount" column is not an original column in the source table, but a measure i defined in the matrix visual. i tried to refer to that as [Amount] instead of 'table'[Amount] but that does not work, because SUM() works on columns 😞

also, i must confess i don't quite follow the logic (new to DAX and all...). it seems that in this new measure, all the hierarchy levels above DATE are populated with the same value, which is the sum of all numeric representation of the included dates. is that correct?

[tex628]

Simply replace the entire SUM() statement with your measure and i believe that it should work!

Measure 2 = IF( SELECTEDVALUE('Table (2)'[Date]) = BLANK() , [Measure] , SELECTEDVALUE('Table (2)'[Date]))

The idea with this measure, is to identify the individual rows that correlate to a specific date and return something other than the intended measure. The aim is to create a to have the individual dates return numeric values that correspond to their order, while returning the actual measure calculation for every other row. 

Br, 
J

[orbs]

ok i see, thank you!

so far so good for the syntax to refer to a measure in the visual, instead of a column in the source table. yay 🙂

however the other issues still exist:

1) all of the category levels inside a specific date also receive that date numeric value, instead of their measure (date is not the last hierarchy level). empirically i see they are sorted alphabetically, while i wish they would be sorted by their measure.

2) if a category level has just one date inside (even if due to filter), that category level also receive that date numeric value, and so do all levels above it, all the way to the top.

it may be that the method to detect the date row if not quite sufficient. can you explain how that condition works?

could it be related to that the measure i'm using is not a sum of stuff, but a ratio between two columns?

[tex628]

I might not have the time today to provide the complete solution but i'll run you through the core idea of the dax we need to do this. 

The core part of the measure is the SELECTEDVALUE() syntax. SELECTEDVALUE evaluates a column, and if there is only a single value in that column, you get that value - otherwise you get BLANK(). 

The measure that we use in the matrix will be evaluated on every row in the matrix, both totals and individual rows. Depending on where you stand in the heirarchy SELECTEDVALUE(Table[Date]) will return either the date, or blank(). 

If it's evaluated next to an individual date, there will only be one date in the datecolumn during the calculation which will be returned. If it's evaluated in the grand-total, there will be many dates present in the date column, meaning that the selectedvalue will return BLANK() instead. 

This is basically how we differentiate where in the row-heirachy we are.

Concerning your issues. 

1. If there is only one date present in a category higher up you will get the date instead of the Measure value. This you can work around using an IF-statement and a rowcount for the datetable.

2. Regarding lower levels of the hierarchy it's a bit difficult to propose something without actually knowing the exact structure. But generally you would use the same logic as with the dates, to identify if you want to return the measure value or something else. 


I'll get back to you tomorrow and we can take a look at how exactly we'll solve this! 

Br,
J

[orbs]

sorry, i didn't think this seemingly-simple question would evolve into such a mess... 🙂

you say that:

"SELECTEDVALUE evaluates a column, and if there is only a single value in that column, you get that value - otherwise you get BLANK(). "

i think the "only a single value" part is the chief cause of the issue. perhaps a different function should be used to determine the hierarchy level? i stumbled upon the IsInScope function, going to investigate.

thanks again for all your effort!

[orbs]

ok, so IsInScope() is actually quite useful - it returns true if the row is a date row or below in hierarchy. that solves for the levels *above* the date row. now focusing on identifying rows *below* the date row...

[tex628]

Something along the lines of this is what I'm thinking should work for the sorting. 



Incorporate the next level of the hierarchy into the sorting calculation to return the correct values for those rows! 

Br,
J

[orbs]

correction: in this new measure, all the hierarchy levels *BELOW* DATE are populated with the numeric value of the date in which they belong. and if i have an item with only one DATE row inside, that item also accepts that date numeric value.

[tex628]

Lets see if I understand you correctly, 



Taking this image as an example. You want the cateogry to be sorted by the Totals. But the dates should be sorted by the order of the dates instead of the individual amounts?

Br,
J