Forum Discussion
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...
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.
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 )
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.
ID Date Status Result A 1/01/2020 R R B 1/01/2020 R A C 1/01/2020 G G A 1/02/2020 G R B 1/02/2020 R A C 1/02/2020 G G A 1/03/2020 R R B 1/03/2020 A A C 1/03/2020 G G 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.
