Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Name | Date of Birth | Version | Remarks | Modified Date |
Phoebe Parker | 28/1/2002 | 1 | Hired | 28/1/2008 |
Krystal Pratt | 14/5/1981 | 1 | Hired | 28/2/2008 |
Krystal Pratt | 14/5/1981 | 2 | Resigned | 26/7/2009 |
Ayla Bartlett | 23/2/1982 | 1 | Hired | 22/1/2010 |
Ayla Bartlett | 23/2/1982 | 2 | Change of title | 24/4/2011 |
Ayla Bartlett | 23/2/1982 | 3 | Resigned | 10/5/2011 |
Yurem Bridges | 11/12/1983 | 1 | Hired | 9/1/2013 |
Yurem Bridges | 11/12/1983 | 2 | Change of address | 24/7/2014 |
Mariyah Cross | 7/3/1986 | 1 | Hired | 8/2/2015 |
Yurem Bridges | 11/12/1983 | 3 | Change of department | 27/7/2015 |
Alonso Leach | 15/5/2004 | 1 | Hired | 3/9/2015 |
Yurem Bridges | 11/12/1983 | 4 | Change of title | 3/9/2015 |
Mariyah Cross | 7/3/1986 | 2 | Resigned | 28/9/2015 |
Elliot Franklin | 13/5/1986 | 1 | Hired | 4/4/2016 |
Dakota Sheppard | 24/6/1986 | 1 | Hired | 23/10/2016 |
Gwendolyn Bush | 9/12/2002 | 1 | Hired | 23/10/2016 |
Rachael Blankenship | 30/9/1987 | 1 | Hired | 10/1/2017 |
Owen Hickman | 1/8/1991 | 1 | Hired | 2/3/2018 |
Rachael Blankenship | 30/9/1987 | 2 | Change of title | 2/4/2018 |
Jasmin Kim | 12/10/1988 | 1 | Hired | 8/11/2018 |
Rachael Blankenship | 30/9/1987 | 3 | Change of department | 21/7/2019 |
Malachi Delgado | 31/12/1992 | 1 | Hired | 30/3/2021 |
Desiree Murphy | 12/1/1994 | 1 | Hired | 7/7/2021 |
Elijah Webb | 25/3/2002 | 1 | Hired | 7/7/2021 |
Haylie Saunders | 13/5/1994 | 1 | Hired | 12/8/2021 |
Amelia Meza | 20/7/1995 | 1 | Hired | 31/8/2021 |
Callie Campbell | 19/6/2005 | 1 | Hired | 31/8/2021 |
Jerome Sloan | 9/10/1995 | 1 | Hired | 2/1/2022 |
James Stone | 17/7/2000 | 1 | Hired | 4/1/2022 |
and if i filter for 2021 below is the expected result that i need.
Name | Date of Birth | Version | Remarks | Modified Date |
Phoebe Parker | 28/1/2002 | 1 | Hired | 28/1/2008 |
Alonso Leach | 15/5/2004 | 1 | Hired | 3/9/2015 |
Yurem Bridges | 11/12/1983 | 4 | Change of title | 3/9/2015 |
Elliot Franklin | 13/5/1986 | 1 | Hired | 4/4/2016 |
Dakota Sheppard | 24/6/1986 | 1 | Hired | 23/10/2016 |
Gwendolyn Bush | 9/12/2002 | 1 | Hired | 23/10/2016 |
Owen Hickman | 1/8/1991 | 1 | Hired | 2/3/2018 |
Jasmin Kim | 12/10/1988 | 1 | Hired | 8/11/2018 |
Rachael Blankenship | 30/9/1987 | 3 | Change of department | 21/7/2019 |
Malachi Delgado | 31/12/1992 | 1 | Hired | 30/3/2021 |
Desiree Murphy | 12/1/1994 | 1 | Hired | 7/7/2021 |
Elijah Webb | 25/3/2002 | 1 | Hired | 7/7/2021 |
Haylie Saunders | 13/5/1994 | 1 | Hired | 12/8/2021 |
Amelia Meza | 20/7/1995 | 1 | Hired | 31/8/2021 |
Callie Campbell | 19/6/2005 | 1 | Hired | 31/8/2021 |
or if i filter 2017 i will expect this
Name | Date of Birth | Version | Remarks | Modified Date |
Phoebe Parker | 28/1/2002 | 1 | Hired | 28/1/2008 |
Alonso Leach | 15/5/2004 | 1 | Hired | 3/9/2015 |
Yurem Bridges | 11/12/1983 | 4 | Change of title | 3/9/2015 |
Elliot Franklin | 13/5/1986 | 1 | Hired | 4/4/2016 |
Dakota Sheppard | 24/6/1986 | 1 | Hired | 23/10/2016 |
Gwendolyn Bush | 9/12/2002 | 1 | Hired | 23/10/2016 |
Rachael Blankenship | 30/9/1987 | 1 | Hired | 10/1/2017 |
Solved! Go to Solution.
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))
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))
Anyone?
You'll need the following measures :-
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
88 | |
82 | |
64 | |
49 |
User | Count |
---|---|
125 | |
111 | |
88 | |
69 | |
66 |