topic Re: Repeat text in a new calculated column based on conditions in DAX Commands and Tips

Repeat text in a new calculated column based on conditions

hbgv123 — Thu, 08 Feb 2024 09:05:37 GMT

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

Re: Repeat text in a new calculated column based on conditions

FreemanZ — Thu, 08 Feb 2024 09:19:20 GMT

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:

Re: Repeat text in a new calculated column based on conditions

govindarajan_d — Thu, 08 Feb 2024 09:41:23 GMT

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