Forum Discussion

deb_power123's avatar
deb_power123
Helper V
5 years ago
Solved

DAX corresponding to conditional column for DIRECT query-SWITCH CASE

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:

 

StudentIDStudentNameAttendanceDateStudentTypelocationflag
100Mary02-05-2011 10:45FulltimeGate12
100Mary02-05-2011 12:45FulltimeGate21
100Mary02-05-2011 09:45FulltimeGate3 
100Mary02-05-2011 08:45FulltimeGate3 
101John02-05-2011 10:59Part TimeGate22
101John02-05-2011 12:56Part TimeGate11
101John02-05-2011 09:55Part TimeGate5 
101John02-05-2011 06:35Part TimeGate1 
101John02-05-2011 10:48Part TimeGate3 
102Tom03-05-2011 09:55Part TimeGate3 
102Tom03-05-2011 10:48Part TimeGate12
102Tom03-05-2011 09:55Part TimeGate1 
102Tom03-05-2011 10:47Part TimeGate1 
102Tom03-05-2011 13:48Part TimeGate21

 

Expected: output should look like below

 

 

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

regards

Sameer

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

2 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    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!