Is it possible to sort the values in a column in a matrix?

[niyati_61]

Hello @d_gosbell ,

I'm currently using Power BI Report Server Desktop (Jan 2022) Version. 

I've created this Matrix visual. I also have a date filter to filter the data as required.

First question, is it possible to sort the values in the Column A in the Matrix? Currently I can only sort by the first ID column.

Second question, is it possible to filter out the blank values in the column A? Currently, I tried advanced filtering and filtered out 'is not blank' values, but it is not working. The blank values are happening because of this WC Column. It is a measure I created. It returns all values based on all the dates. The thing is when I apply a filter on the date for a week, I see blank values on the other columns. 

[d_gosbell]

---

@niyati_61 wrote:

First question, is it possible to sort the values in the Column A in the Matrix? Currently I can only sort by the first ID column.

---

No, you cannot currently sort by a column in a matrix

---

@niyati_61 wrote:

Second question, is it possible to filter out the blank values in the column A? Currently, I tried advanced filtering and filtered out 'is not blank' values, but it is not working. The blank values are happening because of this WC Column. It is a measure I created. It returns all values based on all the dates. The thing is when I apply a filter on the date for a week, I see blank values on the other columns. 

---

One fix here is to change your measure for the WC column so that it also returns blank if the other values for that date are also blank, then it will filter itself out automatically.

[niyati_61]

Hi @d_gosbell ,

Thanks for answering!

To add on the second question I asked, this is the formula I used to create the Measures.

I created this measure first,

C_LENGTH divided by B_LENGTH = DIVIDE(SUM('Table'[C]),SUM('Table'[B]),0)

Then I used the above measure in the WC Measure formula below,

WC = SWITCH(TRUE(),
'Table'[C_LENGTH divided by B_LENGTH] > 0.00499, "D",
'Table'[C_LENGTH divided by B_LENGTH] > 0.003, "C",
'Table'[C_LENGTH divided by B_LENGTH] > 0.0021, "B",
'Table'[C_LENGTH divided by B_LENGTH] < 0.0020, "A", "null")

I've created these measures so that the values returned are dynamically calculated according to the date filter I apply. That's why I did not create them as columns, because then the values as per the date filter were just getting summed up, which was incorrect.

Can you explain further? How do I modify my current measure formula to return this result below?

One fix here is to change your measure for the WC column so that it also returns blank if the other values for that date are also blank, then it will filter itself out automatically.

[niyati_61]

Hello @d_gosbell , any update on the above? Please do let me know if there's a solution.

Thanks for your support in advance!

[d_gosbell]

You have defaulted the divide so that if either column B or C is null that it will return 0 so that your WC will then return "A" if either of these are null

If you remove the default of 0 this measure: 

C_LENGTH divided by B_LENGTH = DIVIDE(SUM('Table'[C]),SUM('Table'[B]))

Then adjust the WC measure to return a blank instead of the string "null" then I think this *might* fix your issue, but I don't have access to your data so I can't really test this.

WC = SWITCH(TRUE(),
'Table'[C_LENGTH divided by B_LENGTH] > 0.00499, "D",
'Table'[C_LENGTH divided by B_LENGTH] > 0.003, "C",
'Table'[C_LENGTH divided by B_LENGTH] > 0.0021, "B",
'Table'[C_LENGTH divided by B_LENGTH] < 0.0020, "A", BLANK())

[niyati_61]

Ah I updated the 2 measure formulas as you said above, however it didn't work.

When I filter the date filter - I'm still getting blanks in the other columns. Same as before. I'm not sure what I can fix now. Any further suggestions?

Can you possibly test this on a sample table like the below? You can use a date filter based on the date below.

Basically, we want to see, for example if you filter the date filter in the week 7th to 13th October 2023, the first 2 rows should not appear in the table itself.

ID

A

B

C

%

WC

Date

110	243.75	243.75	0.00	0.00%	A	07/09/2023
119	159.60	0.00	0.00	0.00%	A	10/09/2023
118	478.80	478.80	1.50	0.31%	C	11/10/2023
117	359.03	239.33	7.60	3.18%	D	10/10/2023
120	159.60	319.20	0.00	0.00%	A	05/10/2023

I really appreciate your assistance!

[d_gosbell · Accepted solution]

OK, so this is happening because blank is numerically equivalent to 0 so it is less then 0.0020 and is returning "A" so if you change the WC measure to test for blanks it should now work

WC = SWITCH(TRUE(),

ISBLANK('Table'[C_LENGTH divided by B_LENGTH]), BLANK(),

'Table'[C_LENGTH divided by B_LENGTH] > 0.00499, "D",

'Table'[C_LENGTH divided by B_LENGTH] > 0.003, "C",

'Table'[C_LENGTH divided by B_LENGTH] > 0.0021, "B",

'Table'[C_LENGTH divided by B_LENGTH] < 0.0020, "A")

[niyati_61]

Hi @d_gosbell ,

Yes, this solution worked out finally! I hadn't thought of this before, great insight!

Thanks a lot for taking time to work on this. I really appreciate it!