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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors