Forum Discussion
Solved
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
| 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 |
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))
4 Replies
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'))RETURNIF(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 Anonymous and KeyurPatel14
thanks for your guidance, is it possible to filter it further to just one record of the latest version per name?
Anyone?
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))
