Sort Matrix Table based on Count

[newgirl]

Hi, guys!

I have a current PBI report that shows a matrix table listing down each complaint ticket and the details of these tickets, with a calendar slicer that lets the reader select any period they want to view the tickets for that period. The reader also sorts the matrix sometimes, could be according to date or according to Person Responsible, etc.

It works fine for now but then a manager requests that the table should have an option that can be sorted based on the count of complaints that a client has. 

My plan is to create a measure that would count how many tickets that a client has based on the matrix table shown and add this measure at the end of the matrix table so that the reader can just sort it based on that, if he wants.

However, I don't know how to create that certain measure that would only depend on the data shown in the matrix since I have a slicer involved. Based on what I've read, they usually add a calculated column but then again, the info on my matrix table depends on a calendar slicer.

Below are samples or if you have another idea, I would like to hear it.

Below is a sample raw data.

Complaint Ticket No.	Client	Date Requested	Person Responsible
1	A	1/1/2020	Jane
2	A	1/5/2020	Jane
3	B	2/1/2020	John
4	C	12/1/2020	Juniper
5	D	5/1/2020	Jane
6	A	3/15/2020	John
7	B	4/15/2020	Juniper
8	A	6/30/2020	John

Below is the sample matrix table that shows in my PBI report

Ex. Slicer is set from Jan 1, 2020 to April 30, 2020

Complaint Ticket No.	Client	Date Requested	Person Responsible
1	A	1/1/2020	Jane
2	A	1/5/2020	Jane
3	B	2/1/2020	John
6	A	3/15/2020	John
7	B	4/15/2020	Juniper

Below is my idea to add the measure in the matrix table. In this case, count of 3 for Client A rows since you can count 3 cases in the matrix. Count of 2 for Client B rows since there are 2 cases in the matrix.

Complaint Ticket No.	Client	Date Requested	Person Responsible	Count
1	A	1/1/2020	Jane	3
2	A	1/5/2020	Jane	3
3	B	2/1/2020	John	2
6	A	3/15/2020	John	3
7	B	4/15/2020	Juniper	2

So that the reader can sort it according to number of complaints

Complaint Ticket No.	Client	Date Requested	Person Responsible	Count
1	A	1/1/2020	Jane	3
2	A	1/5/2020	Jane	3
6	A	3/15/2020	John	3
3	B	2/1/2020	John	2
7	B	4/15/2020	Juniper	2

[newgirl]

Thank you so so so much @MFelix  for all the help! You're an angel!!!

[MFelix]

Hi @newgirl ,

Add the following measure to your model:

Responsible count =
CALCULATE (
    COUNT ( 'Table'[Complaint Ticket No.] );
    FILTER (
        ALLSELECTED (
            'Table'[Client];
            'Table'[Complaint Ticket No.];
            'Table'[Date Requested];
            'Table'[Person Responsible]
        );
        'Table'[Client] = SELECTEDVALUE ( 'Table'[Client] )
    )
)

Should work has expected:

[newgirl]

Hi @MFelix !

Thanks for replying!

I tried to do the measure on my own data but it said something like all tables inside the ALLSELECTED (?) must be the same. Then I realized that I wasn't able to include in my post that I have a Calendar table and the calendar table is what I use as my date slicer in the report, since I also need the Calendar to connect to other transactional data. 

How can I modify the measure to incorporate the Calendar table?

[MFelix]

Hi @newgirl ,

I assume you have the relationship between both table made by the Date Requested and calendar like this:

Calendar Date 1 - > * Date Requested

If this is the relationship then no need to change anything in the measure I have given you. just replace the Table by the name of your correct table that has the requests information.

[newgirl]

Hi, @MFelix  Thank you so much for your help! I've tried it and it's working perfectly!

I'm just so sorry to add one last question. Apparently, they wanted to include the status of the ticket whether it has been closed or open and they want to include that in the sorting too.

For example, this is the current table that's already working well.

They wanted to add the STATUS colum in the table and they wanted the order of the sorting to be all OPEN first and then the number of complaints per client. So if I add the Status, and sort it first before sorting the Responsible Count...

The Responsible Count for Client A for OPEN complaints should now be 2 instead of 3 while the count for its CLOSED complaints should now be 1 instead of 3. 

How can I incorporate the Status in the measure then?

[MFelix · Accepted solution]

Hi @newgirl ,

You just need to add the Status to the ALLSELECTED part of the formula should be similar to this:

Responsible count =
CALCULATE (
    COUNT ( 'Table'[Complaint Ticket No.] );
    FILTER (
        ALLSELECTED (
            'Table'[Client];
            'Table'[Complaint Ticket No.];
            'Table'[Date Requested];
            'Table'[Person Responsible];
            'Table'[Status]
        );
        'Table'[Client] = SELECTEDVALUE ( 'Table'[Client] )
        &&    'Table'[Status] = SELECTEDVALUE ( 'Table'[Status] )
    )
)