Forum Discussion

Hayleysea's avatar
Hayleysea
Icon for Resolver II rankResolver 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 iss...
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.

     

Hayleysea's avatar
Hayleysea
Icon for Resolver II rankResolver 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
Icon for Community Support rankCommunity 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.