Forum Discussion

report_freak's avatar
report_freak
Frequent Visitor
3 years ago
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...
Need Help
Tips and Tricks
  • report_freak's avatar
    report_freak
    3 years ago

    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))

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

 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.
report_freak's avatar
report_freak
Frequent Visitor
3 years ago

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?