extend table information / omitting redundant information

[Bleppich]

Hello,
I have a table with historical data where each entry (ID) has date and a corresponding state.

1234	01.01.2020	True
1234	02.01.2020	True
1234	03.01.2020	True
9999	01.01.2020	True
9999	02.01.2020	False

For a lot of IDs the state never changes (1234 is always True) and I was wondering if I could "extend the data" so that I only have to store/load a single line. Meaning I would just have the latest value for ID = "1234" (yes) and could "calculate" the values for the remaining dates by just using the current value.
I want to filter visuals with this value and use the date column as an axis.
Do I need to work with "Valid_from" an "Valid_till" columns? Because that would get tricky when it comes to the axis

[Bleppich]

Sorry, I think I didn't make myself clear. I know how to sort the values out. I actually want the opposite. To get from a single line in the table to multiple.

from:

1234

01.01.2020

True

to this:

1234	01.01.2020	True
1234	01.02.2020	True
1234	01.03.2020	True
...	....	...

[Anonymous]

HI @Bleppich,

You can try to use the following measure formula to check current row status and return flag. Then you can use this on visual level filter to filter records:

IsDisplay = 
VAR currDate =
    MAX ( 'Table'[Date] )
VAR statusList =
    CALCULATETABLE (
        VALUES ( 'Table'[Status] ),
        ALLSELECTED ( 'Table' ),
        VALUES ( 'Table'[ID] )
    )
VAR Uniqueflag =
    IF ( COUNTROWS ( statusList ) = 1, 1, 0 )
VAR _lastDate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER ( ALLSELECTED ( 'Table' ), [Status] IN statusList ),
        VALUES ( 'Table'[ID] )
    )
RETURN
    IF ( Uniqueflag = 1 && currDate <> _lastDate, 0, 1 )

Regards,

Xiaoxin Sheng