DAX corresponding to conditional column for DIRECT query-SWITCH CASE

[deb_power123]

Hi All,

Can someone suggest me a DAX measure for the below input data which will allow me to seggregate the LastAttendanceDate column and SecondLastAttendanceDate column andLastAttendanceDateLocation column and SecondLastAttendanceDateLocation column from my input datasource based on the flag 1 and 2 values in flag column. flag 1 denotes last attendance date column and lastattendancedate, while flag 2 denotes secondlast attendance date column and second last attendance date location

We can easily achieve this using conditional column concept but I am using direct query for SQL datasource so it throws error for conditional column.

Please suggest how can i seggregate this using DAX measure?

I tried uisng SWITCH case and IF statements in DAX, no luck as it didnt fetch the results since it didnt allow me to pass column values.Only scalar values allowed in IF and switch.I am not sure how can I handle this

Input sample datasource:

StudentID	StudentName	AttendanceDate	StudentType	location	flag
100	Mary	02-05-2011 10:45	Fulltime	Gate1	2
100	Mary	02-05-2011 12:45	Fulltime	Gate2	1
100	Mary	02-05-2011 09:45	Fulltime	Gate3
100	Mary	02-05-2011 08:45	Fulltime	Gate3
101	John	02-05-2011 10:59	Part Time	Gate2	2
101	John	02-05-2011 12:56	Part Time	Gate1	1
101	John	02-05-2011 09:55	Part Time	Gate5
101	John	02-05-2011 06:35	Part Time	Gate1
101	John	02-05-2011 10:48	Part Time	Gate3
102	Tom	03-05-2011 09:55	Part Time	Gate3
102	Tom	03-05-2011 10:48	Part Time	Gate1	2
102	Tom	03-05-2011 09:55	Part Time	Gate1
102	Tom	03-05-2011 10:47	Part Time	Gate1
102	Tom	03-05-2011 13:48	Part Time	Gate2	1

Expected: output should look like below

P.S: My datasource is SQL and I use direct query

regards

Sameer

[v-kelly-msft · Accepted solution]

Hi  @deb_power123 ,

Create 4 measures as below:

LastAttendanceDate = CALCULATE(MAX('Table'[AttendanceDate]),FILTER(ALL('Table'),'Table'[StudentID]=MAX('Table'[StudentID])&&'Table'[flag]=1))

LastAttendanceDateLocation = CALCULATE(MAX('Table'[location]),FILTER(ALL('Table'),'Table'[StudentID]=MAX('Table'[StudentID])&&'Table'[flag]=1))

SecondLastAttendanceDate = CALCULATE(MAX('Table'[AttendanceDate]),FILTER(ALL('Table'),'Table'[StudentID]=MAX('Table'[StudentID])&&'Table'[flag]=2))

SecondLastAttendanceDateLocation = CALCULATE(MAX('Table'[location]),FILTER(ALL('Table'),'Table'[StudentID]=MAX('Table'[StudentID])&&'Table'[flag]=2))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[deb_power123]

Thankyou , awesome approach