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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sum IF with Filter

Hi, 
I have a transaction table (that is connected with Business Central) which has the following relevant  columns among others: Posting Date col, the name of the associates, Category,  and a column with number of hours worked.  I have been trying to write a measure through which I want sum the number of worked hours if date is after December 1, 2021 and the category columns are filtered based on certain conditions.  Here is what I came up with:

 
Total Worked Hours =
IF(
MAX(
'Ärendetransaktioner_Excel'[Posting_Date]
>=
DATE(2021,12,01)
),
CALCULATE(
SUM(
'Ärendetransaktioner_Excel'[Quantity]
),
FILTER( 'Ärendetransaktioner_Excel', 'Ärendetransaktioner_Excel'[Entry_Type] ="usage"
),
FILTER ('Ärendetransaktioner_Excel', 'Ärendetransaktioner_Excel' [Type] = "Resource"
),
FILTER( 'Ärendetransaktioner_Excel', 'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE()
),
"-"
)
)
 
It didn't quite work out  as I get an Error message "MAX function only accepts a column reference as argument".   I am not quite sure what the issue is even if I have spent hours researching the topic.  I would greatly appreciate if I could get som hints here. 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thansk for your feedback @AlexisOlson.

I tried your code and even though it worked, it gave me a different result that I could not really verify.  However, when I realized that the filtering of the Poste_Date column in my transaction table is no different than the other filterings then I wrote the following and got the right result: 

 CALCULATE(
    SUM(
        'Ärendetransaktioner_Excel'[Quantity]
        ),
        'Ärendetransaktioner_Excel'[Entry_Type] ="usage", 
        'Ärendetransaktioner_Excel' [Type] = "Resource",
        'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE(),
        'Ärendetransaktioner_Excel'[Posting_Date] >= DATE(2021,12,01)
    )
    

 🙂 

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

The basic problem appears to be mismatching parentheses.

 

Instead of this

MAX ( Excel[Posting_Date] >= DATE ( 2021, 12, 01 ) )

 I think you want this:

MAX ( Excel[Posting_Date] ) >= DATE ( 2021, 12, 01 )

assuming [Posting_Date] is a table column and not a measure.

 

Another issue is that IF needs to return the same data type for both branches, not a numeric quantity in some cases, and text "-" for others.

 

Try this:

Total Worked Hours =
IF (
    MAX ( 'Ärendetransaktioner_Excel'[Posting_Date] ) >= DATE ( 2021, 12, 01 ),
    CALCULATE (
        SUM ( 'Ärendetransaktioner_Excel'[Quantity] ),
        KEEPFILTERS ( 'Ärendetransaktioner_Excel'[Entry_Type] = "usage" ),
        KEEPFILTERS ( 'Ärendetransaktioner_Excel'[Type] = "Resource" ),
        KEEPFILTERS ( 'Ärendetransaktioner_Excel'[Chargeable_PGS] = TRUE () )
    ),
    0
)
Anonymous
Not applicable

Thansk for your feedback @AlexisOlson.

I tried your code and even though it worked, it gave me a different result that I could not really verify.  However, when I realized that the filtering of the Poste_Date column in my transaction table is no different than the other filterings then I wrote the following and got the right result: 

 CALCULATE(
    SUM(
        'Ärendetransaktioner_Excel'[Quantity]
        ),
        'Ärendetransaktioner_Excel'[Entry_Type] ="usage", 
        'Ärendetransaktioner_Excel' [Type] = "Resource",
        'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE(),
        'Ärendetransaktioner_Excel'[Posting_Date] >= DATE(2021,12,01)
    )
    

 🙂 

amitchandak
Super User
Super User

@Anonymous , Try like

 

Total Worked Hours =
IF(
MAX('Ärendetransaktioner_Excel'[Posting_Date]) >= DATE(2021,12,01) ,
CALCULATE(
SUM(
'Ärendetransaktioner_Excel'[Quantity]
),
FILTER( 'Ärendetransaktioner_Excel', 'Ärendetransaktioner_Excel'[Entry_Type] ="usage" && 'Ärendetransaktioner_Excel' [Type] = "Resource" && 'Ärendetransaktioner_Excel'[Chargeable_PGS] =TRUE() )
)
)

Anonymous
Not applicable

Thanks for your quick response.   && makes the code cleaner, but is not solving the problem with the first argument of my IF statement.   Still get error message!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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