Forum Discussion
Solved
Path Function with filter
I need to create the hierachy for user and supervisor based on the date and account. Thanks in advance
Hi sowmyar ,
Seems cant use calculate with Path to get the result you need.
I create another 3 columns to get what you need:
mark = var _maxid=CALCULATE(MAX('Table'[EmployeeId]),ALL('Table')) var _min=CALCULATE(MIN('Table'[EmployeeId]),FILTER('Table','Table'[Date]<>EARLIER('Table'[Date])&&'Table'[EmployeeId]=EARLIER('Table'[Supervisor ID]))) Return _minColumn 2 = IF('Table'[mark]<>BLANK(),CALCULATE(MAX('Table'[Column]),FILTER('Table','Table'[EmployeeId]=EARLIER('Table'[mark]))))Column 3 = var _len1=LEN('Table'[Column]) var _len2=LEN('Table'[Column 2]) var _res=RIGHT('Table'[Column],_len1-_len2+1) Return IF('Table'[Column 2]=BLANK(),'Table'[Column],_res)And you will see:
I guess column3 is what you need.
For the related .pbix file,pls see attached.
Best Regards,
KellyDid I answer your question? Mark my reply as a solution!
but when there is a date field,few employees will have two supervisor based on the date,in this case i need to finf the hierachy based on each date
Hi sowmyar ,
Seems cant use calculate with Path to get the result you need.
I create another 3 columns to get what you need:
mark =
var _maxid=CALCULATE(MAX('Table'[EmployeeId]),ALL('Table'))
var _min=CALCULATE(MIN('Table'[EmployeeId]),FILTER('Table','Table'[Date]<>EARLIER('Table'[Date])&&'Table'[EmployeeId]=EARLIER('Table'[Supervisor ID])))
Return
_minColumn 2 =
IF('Table'[mark]<>BLANK(),CALCULATE(MAX('Table'[Column]),FILTER('Table','Table'[EmployeeId]=EARLIER('Table'[mark]))))Column 3 =
var _len1=LEN('Table'[Column])
var _len2=LEN('Table'[Column 2])
var _res=RIGHT('Table'[Column],_len1-_len2+1)
Return
IF('Table'[Column 2]=BLANK(),'Table'[Column],_res)And you will see:
I guess column3 is what you need.
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my reply as a solution!