The data I am working has a parent sequence number and a child sequence number. I want to be able to return the max sequence number filtered on a date.
I have tried many diffent ways to accomplish this with no success.
I have been able to count the child sequence number with a filter on the parent sequence number and the date):
Test = calculate(counta('TABLE'[CHILD_SEQ]),FILTER(ALL('TABLE'),'TABLE'[PARENT_SEQ]=EARLIER('TABLE'[PARENT_SEQ]) && 'TABLE'[CHILD_SEQ_DATE]=EARLIER('TABLE'[CHILD_SEQ_DATE])))
This returns a count of child sequence number that are on the same date. I now want to return the maximum child sequence number on the row that has the maximum child sequence number and nothing for any row that is not a maximum, based on the filter above.
Everytime I try to use an if statement i get: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value