Repeat text in a new calculated column based on conditions

[hbgv123]

Hi,

I am kind of stuck in this simple issue. I thought of using (calculate max all except) to arrive at the solution but somehow text values are not recognized. Can you please check this issue. I am so used to LOD min condition in tableau.

Current structure

Product	Country	Rep
Furnitures	US	Rep-1
Furnitures	CA	Rep-2
Furnitures	GB	Rep-3
Furnitures	FR	Rep-4
Furnitures	IT	Rep-5
Electronics	GB	Rep-11
Electronics	CA	Rep-21
Electronics	US	Rep-31
Electronics	FR	Rep-41
Electronics	IT	Rep-51

Expected output with calculated column Lead (DAX)

Lead is updated based on rep for each product from Country US

Product	Country	Rep	Lead
Furnitures	US	Rep-1	Rep-1
Furnitures	CA	Rep-2	Rep-1
Furnitures	GB	Rep-3	Rep-1
Furnitures	FR	Rep-4	Rep-1
Furnitures	IT	Rep-5	Rep-1
Electronics	GB	Rep-11	Rep-31
Electronics	CA	Rep-21	Rep-31
Electronics	US	Rep-31	Rep-31
Electronics	FR	Rep-41	Rep-31
Electronics	IT	Rep-51	Rep-31

[FreemanZ · Accepted solution]

hi @hbgv123 ,

you can add a calculated column like:

Column = 
MAXX(
    FILTER(
        data,
        data[Product]=EARLIER(data[Product])
            &&data[Country]="US"
    ),
    data[Rep]
)

it worked like:

[govindarajan_d]

Hi @hbgv123 ,

Explanation: We use FILTER function to create a virtual table that contains Rep for each Product's US Rep. Then we use the FIRSTNONBLANK function to get the value of the Rep. You can think of it as a MIN or MAX for text. 

Lead =
VAR __Product = Data[Product]
VAR __Result =
    FIRSTNONBLANK (
        SELECTCOLUMNS (
            FILTER ( ALL ( Data ), Data[Country] = "US" && Data[Product] = __Product ),
            "Lead", Data[Rep]
        ),
        [Lead]
    )
RETURN
    __Result