topic Max sequence number filtered in DAX Commands and Tips

Max sequence number filtered

rjs2 — Mon, 21 Oct 2019 18:02:31 GMT

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

Thanks!

Re: Max sequence number filtered

rjs2 — Mon, 21 Oct 2019 18:36:26 GMT

I tried this as well:

Test2 = VAR SEQ= CALCULATE(MAX('Vetting Query'[CHILD_SEQ]),ALL('TABLE'),'TABLE'[PARENT_SEQ]=EARLIER('TABLE'[PARENT_SEQ]) && 'TABLE'[CHILD_SEQ_DATE]=EARLIER('TABLE'[CHILD_SEQ_DATE]))

Return

SEQ

This is the error I get:

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

Re: Max sequence number filtered

rjs2 — Mon, 21 Oct 2019 20:01:23 GMT

Took some time, but I solved it. It was easier to stop into it with two columns

First new column:

Max on Day = CALCULATE(MAX('Vetting Query'[CHILD_SEQ]),ALL('TABLE'),'TABLE'[PARENT_SEQ]=EARLIER('TABLE'[PARENT_SEQ]) && 'TABLE'[CHILD_SEQ_DATE]=EARLIER('TABLE'[CHILD_SEQ_DATE]))

This gave me the max sequence number for the child sequence per day on every row related to that parrent seq.

Second new column:

Number Processed = if('TABLE[Max on Day]='TABLE[CHILD_SEQ],'Vetting Query'[CHILD_SEQ],"")

The Number Processed will only have the Max CHILD_SEQ on the row of the Max sequence number and a blank if its not the max sequence number.