Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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.

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.