Create new conditional column

[Syndicate_Admin]

Hello, can someone please help me with DAX?

I want to create a fourth column called "Satatus" where I indicate if the "ID", "Year", "activity" had the same activity. Then the result in the new column "Status" would say: Same, Changed, None

Thanks a lot!! Rosario Pineda.

ID	Year	Activity
1	2019	Yoga
1	2019	Dance
1	2019	Pilates
1	2019	Swimming
1	2020	Dance
1	2020	Pilates
1	2020	Swimming
1	2020	Meditation
2	2019	Yoga
2	2019	Dance
2	2019	Pilates
2	2019	Swimming
2	2020	Yoga
2	2020	Dance
2	2020	Pilates
2	2020	Swimming
3	2019	Yoga
3	2019	Dance
3	2019	Pilates
3	2019	Swimming
3	2020	Yoga
3	2020	Dance
3	2020
3	2020	Swimming

[ryan_mayu · Accepted solution]

you can try this

Column = 
VAR _a=maxx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Year]<>EARLIER('Table'[Year])&&'Table'[Activity]=EARLIER('Table'[Activity])),'Table'[Activity])
return if('Table'[Activity]="","Ninguna",if(_a="","Cambiaron","Misma"))

pls see the attachment below

[mahoneypat]

Not sure this is what you mean, but please try this column expression. Replace Activities with your actual table name.

NewColumn =
VAR thisactivity = Activities[Activity]
VAR thisyear = Activities[Year]
VAR PYactivities =
CALCULATETABLE (
DISTINCT ( Activities[Activity] ),
ALLEXCEPT ( Activities, Activities[ID] ),
Activities[Year] = thisyear - 1
)
RETURN
SWITCH (
TRUE (),
ISEMPTY ( PYactivities ), "None",
thisactivity IN PYactivities, "Same",
"Change"
)

Pat

[ryan_mayu]

@Syndicate_Admin 

i can't find id year and activity has the same value. 

could you pls add the expected output in the sample data you provided?

[Syndicate_Admin]

Sorry I didn't understand your question, I rewuiero new column as the example below:

[ryan_mayu · Accepted solution]

you can try this

Column = 
VAR _a=maxx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[Year]<>EARLIER('Table'[Year])&&'Table'[Activity]=EARLIER('Table'[Activity])),'Table'[Activity])
return if('Table'[Activity]="","Ninguna",if(_a="","Cambiaron","Misma"))

pls see the attachment below

[Syndicate_Admin · Accepted solution]

@ryan_mayu thank you very much for your support, I work percfecto!!

Greetings and blessings. Rosary.

[ryan_mayu]

you are welcome