Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
deb_power123
Helper V
Helper V

DAX to Filter date column and add it to new columns based on flag values for direct query

Hi All,

 

In my below student table I have the following columns:-

StudentID
StudentName
AttendanceDate
StudentType
location
desc

flag

 

I need to create calculated column or DAX measure to find the LastAttendanceDate and SecondLastAttendanceDate and LastAttendanceDate and  SecondLastAttendanceDateLocation based on the flag values in the flag column .

 

Here flag value1 denotes the lastAttendancedate and last attendancelocation  and flagvalue 2 denotes SecondLastAttendanceDate and SecondLastAttendanceLocation row values respectively. 

 

The solution was simple if we needed to evalaute this using conditional column concept but I am using direct query and it wont help. so any DAX measure can work in my case.

 

My input datasource excel is as under:-

StudentIDStudentNameAttendanceDate   StudentType  location      desc         flag
100Mary02-05-2011 10:45Fulltime    Gate1Gate1location         2
100Mary02-05-2011 12:45Fulltime    Gate2Gate2location         1
100Mary02-05-2011 09:45Fulltime    Gate3Gate3location 
100Mary02-05-2011 08:45Fulltime    Gate3Gate3location 
101John02-05-2011 10:59Part Time    Gate2Gate2location        2
101John02-05-2011 12:56Part Time    Gate1Gate1location        1
101John02-05-2011 09:55Part Time   Gate5Gate5location 
101John02-05-2011 06:35Part Time   Gate1Gate1location 
101John02-05-2011 10:48Part Time   Gate3Gate3location 
102Tom03-05-2011 09:55Part Time   Gate3Gate3location 
102Tom03-05-2011 10:48Part Time   Gate1Gate1location         2
102Tom03-05-2011 09:55Part Time   Gate1Gate1location 
102Tom03-05-2011 10:47Part Time   Gate1Gate1location 
102Tom03-05-2011 13:48Part Time   Gate2Gate2location          1

 

Expected output:

 

1.JPG

 

Is there a way to achieve this using DAX filter function ?

 

Please suggest your thoughts .

 

Kind regards

Sameer

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

@deb_power123 , you might want try any one of solutions, ie PQ solution, DAX solution and Excel formula solution, down below,

 

PQ solution

Screenshot 2021-03-12 123933.png

 

DAX solution,

Screenshot 2021-03-12 124854.png

 

Excel formula solution, our oldie but goodie, and also my favorite,

Untitled.png

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

3 REPLIES 3
CNENFRNL
Community Champion
Community Champion

@deb_power123 , you might want try any one of solutions, ie PQ solution, DAX solution and Excel formula solution, down below,

 

PQ solution

Screenshot 2021-03-12 123933.png

 

DAX solution,

Screenshot 2021-03-12 124854.png

 

Excel formula solution, our oldie but goodie, and also my favorite,

Untitled.png

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Thankyou @CNENFRNL your suggested approach worked like a charm.You really made me learn a nice concept today.RANK(1) did the trick.Best solution...Thankyou again, it seggregates all required fields perfectly

Hi @CNENFRNL  thankyou for your reply but in my requirement I have added a flag column denoting flag 1 as row entry for lastAttendanceDate and flag 2 as row entry for SecondLastAttendanceDate.I added the link of my excel as below.

My input datafile 

 

My datasource is using Direct Query so I will require to use DAX measure for this.

 

Could you please suggest any DAX measure to return the same output you provided above but based out of the flag values.

I want to achieve the below output as in screenshot based on the flag values.

123.JPG

Please provide your thoughts.I tried DAX and other approaches of conditional format but it is failing.I am clueless.I am doing it somewhere incorrect, please letme know if you have any inputs based on my data

My input datafile 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors