cascading filtered data to all rows with same id

[Pallekan]

Sample data:

ID	Location	Operation	Current operation
5578	Loc1	10	False
5578	Loc2	20	True
5578	Loc3	30	False
5578	Loc4	40	False
4428	Loc2	10	False
4428	Loc3	20	False
4428	Loc5	30	True
4428	Loc4	40	False

I want to add a row that contains CurrentLocation which would like this:

ID	Location	Operation	Current operation	Current Location
5578	Loc1	10	False	Loc2
5578	Loc2	20	True	Loc2
5578	Loc3	30	False	Loc2
5578	Loc4	40	False	Loc2
4428	Loc2	10	False	Loc5
4428	Loc3	20	False	Loc5
4428	Loc5	30	True	Loc5
4428	Loc4	40	False	Loc5

I have tried the following measure:

Calculate(

Sample Data[Location],

Filter(Sample data, Sample data[Current operation])

)

This unfortunatly does not showcase the location for all rows but only the row with True in currentOperation.

Is there an easy solution I'm missing? Do I have to use summarize in some fashion?

ps: This is done in live connection so only measures are available.

Any insight is appreciated, thank you all!

[FreemanZ · Accepted solution]

hi @Pallekan 

try to

1) create a measure like this:

CurrentLocation = 
VAR _id = MAX(data[ID])
RETURN
MINX(
    FILTER(
        ALL(data),
        data[ID]=_id
            &&data[Current Operation] = TRUE
    ),
    data[Location]
)

2) plot a table visual with the measure and at least the [ID] column.

i tried and it worked like this:

[amitchandak]

@Pallekan , Create a new columns

maxx(filter(Table, [ID] = earlier([ID]) && [Current operation] = "True"), [Location])