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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
report_freak
Frequent Visitor

DAX for filter with modified date and version

hi

 

not sure if this is possible for DAX in powerbi i am trying to do a filter to count the row when the year change and get any latest "version" from that year. example

 

this my table

NameDate of BirthVersionRemarksModified Date
Phoebe Parker28/1/20021Hired28/1/2008
Krystal Pratt14/5/19811Hired28/2/2008
Krystal Pratt14/5/19812Resigned26/7/2009
Ayla Bartlett23/2/19821Hired22/1/2010
Ayla Bartlett23/2/19822Change of title24/4/2011
Ayla Bartlett23/2/19823Resigned10/5/2011
Yurem Bridges11/12/19831Hired9/1/2013
Yurem Bridges11/12/19832Change of address24/7/2014
Mariyah Cross7/3/19861Hired8/2/2015
Yurem Bridges11/12/19833Change of department27/7/2015
Alonso Leach15/5/20041Hired3/9/2015
Yurem Bridges11/12/19834Change of title3/9/2015
Mariyah Cross7/3/19862Resigned28/9/2015
Elliot Franklin13/5/19861Hired4/4/2016
Dakota Sheppard24/6/19861Hired23/10/2016
Gwendolyn Bush9/12/20021Hired23/10/2016
Rachael Blankenship30/9/19871Hired10/1/2017
Owen Hickman1/8/19911Hired2/3/2018
Rachael Blankenship30/9/19872Change of title2/4/2018
Jasmin Kim12/10/19881Hired8/11/2018
Rachael Blankenship30/9/19873Change of department21/7/2019
Malachi Delgado31/12/19921Hired30/3/2021
Desiree Murphy12/1/19941Hired7/7/2021
Elijah Webb25/3/20021Hired7/7/2021
Haylie Saunders13/5/19941Hired12/8/2021
Amelia Meza20/7/19951Hired31/8/2021
Callie Campbell19/6/20051Hired31/8/2021
Jerome Sloan9/10/19951Hired2/1/2022
James Stone17/7/20001Hired4/1/2022

 

and if i filter for 2021 below is the expected result that i need.

NameDate of BirthVersionRemarksModified Date
Phoebe Parker28/1/20021Hired28/1/2008
Alonso Leach15/5/20041Hired3/9/2015
Yurem Bridges11/12/19834Change of title3/9/2015
Elliot Franklin13/5/19861Hired4/4/2016
Dakota Sheppard24/6/19861Hired23/10/2016
Gwendolyn Bush9/12/20021Hired23/10/2016
Owen Hickman1/8/19911Hired2/3/2018
Jasmin Kim12/10/19881Hired8/11/2018
Rachael Blankenship30/9/19873Change of department21/7/2019
Malachi Delgado31/12/19921Hired30/3/2021
Desiree Murphy12/1/19941Hired7/7/2021
Elijah Webb25/3/20021Hired7/7/2021
Haylie Saunders13/5/19941Hired12/8/2021
Amelia Meza20/7/19951Hired31/8/2021
Callie Campbell19/6/20051Hired

31/8/2021

 

or if i filter 2017 i will expect this

NameDate of BirthVersionRemarksModified Date
Phoebe Parker28/1/20021Hired28/1/2008
Alonso Leach15/5/20041Hired3/9/2015
Yurem Bridges11/12/19834Change of title3/9/2015
Elliot Franklin13/5/19861Hired4/4/2016
Dakota Sheppard24/6/19861Hired23/10/2016
Gwendolyn Bush9/12/20021Hired23/10/2016
Rachael Blankenship30/9/19871Hired10/1/2017

 

1 ACCEPTED SOLUTION
report_freak
Frequent Visitor

i manage to resolved it myself, below is the measure dax i used.

 

Headcount = 
//get max filted Date
Var MaxDate=MAX ( Dates[Date] )
//Filter down the Moditifed date to the Max Date
Var FirstFilter = FILTER (Employees, Employees[Modified Date] <= MaxDate)
//Rename Version and Name column so its not confusing
var allrecord = SELECTCOLUMNS(FirstFilter,"allVersion",Employees[Version],"allName",Employees[Name])
//add addtional column to reverse the Version number so that the latest version will always be 1
var RecordsFilter = ADDCOLUMNS(FirstFilter,"VersionR",MAXX(FILTER(allrecord,[allName]=Employees[Name]),[allVersion])+1 - Employees[Version])

return
//Count the rows where Version number = 1
   COUNTROWS(FILTER(RecordsFilter,[VersionR]=1))

 

View solution in original post

4 REPLIES 4
report_freak
Frequent Visitor

i manage to resolved it myself, below is the measure dax i used.

 

Headcount = 
//get max filted Date
Var MaxDate=MAX ( Dates[Date] )
//Filter down the Moditifed date to the Max Date
Var FirstFilter = FILTER (Employees, Employees[Modified Date] <= MaxDate)
//Rename Version and Name column so its not confusing
var allrecord = SELECTCOLUMNS(FirstFilter,"allVersion",Employees[Version],"allName",Employees[Name])
//add addtional column to reverse the Version number so that the latest version will always be 1
var RecordsFilter = ADDCOLUMNS(FirstFilter,"VersionR",MAXX(FILTER(allrecord,[allName]=Employees[Name]),[allVersion])+1 - Employees[Version])

return
//Count the rows where Version number = 1
   COUNTROWS(FILTER(RecordsFilter,[VersionR]=1))

 

report_freak
Frequent Visitor

Anyone? 

punitkhatri
Helper III
Helper III

punitkhatri_0-1690527792463.png

 You'll need the following measures :-

ResultCondition =
VAR selyear = SELECTEDVALUE('Date'[Year])
VAR FilYear =
CALCULATE(
    COUNTROWS(Filterchange),//SUM(Filterchange[Version]),  
       
        Filterchange[Mdyear] <= VALUE(selyear) //VALUE(SELECTEDVALUE('Date'[Year]))
    ,
    ALL('Date')
   
)
RETURN
IF(FilYear <> 0, 1, 0)
 
Versions = CALCULATE(SUM(Filterchange[Version]), ALL('Date'))
 
Now Plot all the columns that you need in a table but use the "Versions" measure as the replacement of the Version column. It will be helpful in not letting the date table filter our visual. And ideally also, we should plot measures instead of columns in the visuals wherever possible.
 
Now apply the ResultCondition measure as a visual level filter on the same visual and set its value = 1.
Helped by @KeyurPatel14  so a special thanks to this guy.
Let me know If this helps or not.

Hi @punitkhatri  and @KeyurPatel14 

 

thanks for your guidance, is it possible to filter it further to just one record of the latest version per name?

report_freak_0-1690539187064.png

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.