Forum Discussion

Hayleysea's avatar
Hayleysea
Resolver II
6 years ago
Solved

Filter rows without filtering date columns

Hi There,

 

I have a matrix with dates for columns and ID's for rows and the values are text inside which only appear when an ID meets a certain condition (inputted into the raw data). 

 

The issue I am having is that I only want ID's to show where the condition is met (value has text) for the latest date column.

 

But if I filter on that condition based only on the latest week all my other date columns dissapear. 

 

Any ideas?

 

I was thinking whether there is a formula that would take the a value from the latest date and match against the ID in earlier dates so that I can filter based on that and that should get rid of the filter issue but I don't know what that formula would look like?

 

Thank you so much in advance

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Hayleysea ,

     

    Please create a calculated column as below.

    Column = 
VAR maxdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER (
            'Table',
            'Table'[Date] = maxdate
                && 'Table'[ID] = EARLIER ( 'Table'[ID] )
        )
    )

     

    For more details, please check the pbix as attached.

     

4 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    Hey Hayleysea ,

     

    please create a pbix file that contains sample data, but still reflects your data model. Upload the pbix file to onedrive or dropbox and share the link. Don't forget to describe the expected result.

     

    Regards,

    Tom

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Hayleysea ,

    You have to something like that. More we can tell with Data

    VAR __id = MAX ( 'Table'[id] )
VAR __date = CALCULATE ( MAX( 'Table'[date] ), ALLSELECTED ( 'Table' ),  'Table'[id] = __id ) 
RETURN CALCULATE ( MAX ( 'Table'[value] ), VALUES ( 'Table'[id] ), 'Table'[id] = __id, 'Table'[date] = __date )
    • Hayleysea's avatar
      Hayleysea
      Resolver II
      6 years ago

      Thanks, data is like this so take the result from the latest date and populate against the matching ID for the other dates. I tried the formula from amitchandak but didn't get the desired result.

      IDDateStatusResult
      A1/01/2020RR
      B1/01/2020RA
      C1/01/2020GG
      A1/02/2020GR
      B1/02/2020RA
      C1/02/2020GG
      A1/03/2020RR
      B1/03/2020AA
      C1/03/2020GG
      • v-frfei-msft's avatar
        v-frfei-msft
        Community Support
        6 years ago

        Hi Hayleysea ,

         

        Please create a calculated column as below.

        Column = 
VAR maxdate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER (
            'Table',
            'Table'[Date] = maxdate
                && 'Table'[ID] = EARLIER ( 'Table'[ID] )
        )
    )

         

        For more details, please check the pbix as attached.