Help With Measure

Hi JayZee ,

Based on your description, you need to add an index column for each ID in power query first, the query is like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyMjAy0DfUNzQCsj2ClGJ10MQNMcQNLfWNIOo9QzDFDRHiTsjmGADZbpl5iXnJqdglUTVBDCNC3BnJHAuEW9GFka12RjLKAsMkdGEXJJMsiRUGG2KJZi+6HMipsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Effective Date" = _t, Department = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Effective Date", type date}, {"Department", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count", each _, type table [ID=nullable text, Effective Date=nullable date, Department=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Effective Date", "Department", "Index"}, {"Custom.Effective Date", "Custom.Department", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.Effective Date", "Effective Date"}, {"Custom.Department", "Department"}, {"Custom.Index", "Index"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Effective Date", type date}, {"Department", type text}, {"Index", Int64.Type}})
in
    #"Changed Type1"

Close and apply it in power query, create a measure like this, put it in the visual filter and set its value as 1:

Visual control =
VAR _Name =
    CALCULATE (
        MAX ( 'Table'[Department] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID]
                IN DISTINCT ( 'Table'[ID] )
                    && YEAR ( SELECTEDVALUE ( 'Date'[Effective Date] ) )
                        = YEAR ( SELECTEDVALUE ( 'Table'[Effective Date] ) )
                    && 'Table'[Effective Date] <= SELECTEDVALUE ( 'Date'[Effective Date] )
                    && 'Table'[Index]
                        = CALCULATE (
                            MIN ( 'Table'[Index] ),
                            FILTER (
                                ALL ( 'Table' ),
                                'Table'[ID] = EARLIER ( 'Table'[ID] )
                                    && YEAR ( SELECTEDVALUE ( 'Date'[Effective Date] ) )
                                        = YEAR ( SELECTEDVALUE ( 'Table'[Effective Date] ) )
                                    && 'Table'[Effective Date] <= SELECTEDVALUE ( 'Date'[Effective Date] )
                            )
                        )
        )
    )
VAR _Date =
    CALCULATE (
        MAX ( 'Table'[Effective Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID]
                IN DISTINCT ( 'Table'[ID] )
                    && YEAR ( SELECTEDVALUE ( 'Date'[Effective Date] ) )
                        = YEAR ( SELECTEDVALUE ( 'Table'[Effective Date] ) )
                    && 'Table'[Effective Date] <= SELECTEDVALUE ( 'Date'[Effective Date] )
                    && 'Table'[Index]
                        = CALCULATE (
                            MIN ( 'Table'[Index] ),
                            FILTER (
                                ALL ( 'Table' ),
                                'Table'[ID] = EARLIER ( 'Table'[ID] )
                                    && YEAR ( SELECTEDVALUE ( 'Date'[Effective Date] ) )
                                        = YEAR ( SELECTEDVALUE ( 'Table'[Effective Date] ) )
                                    && 'Table'[Effective Date] <= SELECTEDVALUE ( 'Date'[Effective Date] )
                            )
                        )
        )
    )
RETURN
    IF (
        _Name = SELECTEDVALUE ( 'Table'[Department] )
            && _Date = SELECTEDVALUE ( 'Table'[Effective Date] ),
        1,
        0
    )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.