Help with DAX

[gauravnarchal]

Hello

How can I write a Dax to get the TYPE against each team?

If TYPE is 6032 or 6031 or 6000 and matches the identifier in the next row return type of the first identifier else TYPE

Thanks

Gaurav

Data

Table 1

Identifier	Type	Team
2043826	6032
2043826	6012	Juniper Team
2043826	309	Pacific Team
2043785	6032
2043785	6012	Pacific Team
2043789	6000
2043789	6012	Mars Team
2043802	6000
2043802	6012	Desert Stallion
2043808	6000
2043808	6012	Pacific Team
2043811	6030
2043811	6012	Juniper Team
2043818	6000
2043818	6012	Mars Team
2043841	6031
2043841	6012	Sun Team
2043844	6000
2043844	6012	Juniper Team

Result

Identifier	Type	Team	Pattern
2043826	6032
2043826	6012	Juniper Team	6032
2043826	309	Pacific Team	309
2043785	6032
2043785	6012	Pacific Team	6032
2043789	6000
2043789	6012	Mars Team	6000
2043802	6000
2043802	6012	Desert Stallion	6000
2043808	6000
2043808	6012	Pacific Team	6000
2043811	6030
2043811	6012	Juniper Team	6030
2043818	6000
2043818	6012	Mars Team	6000
2043841	6031
2043841	6012	Sun Team	6000
2043844	6000
2043844	6012	Juniper Team	6000

[v-stephen-msft · Accepted solution]

Hi @gauravnarchal ,

Here's my solution.

1.Add an index column in Power Query Editor.

2.Add a calculated column in Power BI Desktop.

Pattern =
VAR _previousType =
    CALCULATE (
        MAX ( 'Table'[Type] ),
        FILTER (
            'Table',
            [Identifier] = EARLIER ( 'Table'[Identifier] )
                && [Index] < EARLIER ( 'Table'[Index] )
        )
    )
VAR _previousTeam =
    CALCULATE (
        MAX ( 'Table'[Team] ),
        FILTER (
            'Table',
            [Identifier] = EARLIER ( 'Table'[Identifier] )
                && [Index] < EARLIER ( 'Table'[Index] )
        )
    )
RETURN
    IF (
        ISBLANK ( [Team] ),
        BLANK (),
        IF ( ISBLANK ( _previousTeam ), _previousType, [Type] )
    )

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.