Matrix - Filter by blank in last column

[andy_scott42]

I have a matrix like below:

Room	08/08/2018	15/08/2018	22/08/2018	29/08/2018	05/09/2018
a	1	1			1
b	1	1	1	1	1
c		1	1	1
d	1	1	1	1	1
e	1	1	1

I want to filter the matrix so that I only get rows where the vaue in the last column is blank (in this case is 05/09/2018 however this will change as more dates are added).

I've tried adding a calculated column to find the max date for each room and then adding a measure where the max(date) <> Max date for each Room but it didn't work. 

I've also tried creating a table of Rooms and filtering it where the count of rooms per date is 0 but this also doesn't work,

Any help would be greatly appreciated.

[sess]

Hi There, 

have you found a solution for your problem? I am facing a similar issue. 

[v-piga-msft]

Hi @andy_scott42,

What is your data model like?

Room	08/08/2018	15/08/2018	22/08/2018	29/08/2018	05/09/2018
a	1	1			1
b	1	1	1	1	1
c		1	1	1
d	1	1	1	1	1
e	1	1	1

Or table  model like this?

If you have the table model 1, please transform it to the second table model in Query Editor with Unpivot then you could create the calculated table and the measure.

T3 = 
var temp=CROSSJOIN(VALUES(Table2[Room]),VALUES(Table2[Date]))

    return

        EXCEPT(temp,ALL(Table2[Room],Table2[Date]))

Measure 3 = 

CALCULATE(COUNTROWS('T3'),FILTER('T3','T3'[Date]=MAX('T3'[Date])))

Then you could get the result.

 

More details, you could refer to this attachment.

Best Regards,

Cherry

[andy_scott42]

Thanks a lot for this Cherry. 

My data is already in the format of the second table in your example.

Thanks for the measure. I should be able to use that. 

However, how can I use it to filter the matrix to get the rows where the values in the column with Max(Date) is blank.

For example, I want to add a filter so that my matrix will only show rows c and e as the value for 05/09/2018 is blank?

Thanks

[v-piga-msft]

Hi @andy_scott42,

The T3 table is created for the blank values of the rooms.

So you could create the date slicer with the date column in T3 table and create the matrix with the date and room column in T3 table.

Then you will get the result.

Best  Regards,

Cherry

[andy_scott42]

Thanks @v-piga-msft

I need to display all the columns though. 

So my desired matrix would be filtered to show:

Room	08/08/2018	15/08/2018	22/08/2018	29/08/2018	05/09/2018
c		1	1	1
e	1	1	1

As I want to see the rows where the value in the most recent column is blank.

Thanks for your help.