Forum Discussion

andy_scott42's avatar
andy_scott42
Icon for Helper II rankHelper II
8 years ago

Matrix - Filter by blank in last column

I have a matrix like below:

 

Room08/08/201815/08/201822/08/201829/08/201805/09/2018
a11  1
b11111
c 111 
d11111
e111  

 

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.

 

 

5 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    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's avatar
      andy_scott42
      Icon for Helper II rankHelper II
      8 years ago

      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's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        8 years ago

        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

  • sess's avatar
    sess
    Regular Visitor
    3 years ago

    Hi There, 

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