cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rjs2
Resolver I
Resolver I

Max sequence number filtered

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!
 
RJ
1 ACCEPTED SOLUTION

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.

 

View solution in original post

2 REPLIES 2
rjs2
Resolver I
Resolver I

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.

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.

 

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors