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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
newpbiuser01
Helper IV
Helper IV

Using Filter with a Calculate Function Based on a Condition

Hello,

 

Would anyone know if I can apply an if statement to whether or not the calculate function applies a filter? As an example, I'm trying to say, 

test =
VAR _Category =   SELECTEDVALUE ( 'Table1'[VALUE] )
RETURN
    CALCULATE (  SUM ( 'Table2'[TOTAL SAVINGS] ),
        IF (  _Category <> "All",
            FILTER ( 'Table2', [CATEGORY] = _Category ),
            ALLSELECTED ( 'Table2'[CATEGORY] )
        )
    )

However, I get an error saying "A Function "FILTER" has been used in a True/False experssion that is used as a table filter expression. That is now allowed".

Is there a way to only use filter with a calculate function when a condition is met?

1 ACCEPTED SOLUTION
mickey64
Super User
Super User

How about this formula below?

 

test =
    VAR _Category = SELECTEDVALUE ( 'Table1'[VALUE] )
    RETURN
    IF ( _Category <> "All",
        SUMX ( FILTER('Table2', 'Table2'[CATEGORY] = _Category), 'Table2'[TOTAL SAVINGS] ), 

        CALCULATE ( SUM ( 'Table2'[TOTAL SAVINGS] ), ALLSELECTED ( 'Table2'[CATEGORY] ))
    )

View solution in original post

1 REPLY 1
mickey64
Super User
Super User

How about this formula below?

 

test =
    VAR _Category = SELECTEDVALUE ( 'Table1'[VALUE] )
    RETURN
    IF ( _Category <> "All",
        SUMX ( FILTER('Table2', 'Table2'[CATEGORY] = _Category), 'Table2'[TOTAL SAVINGS] ), 

        CALCULATE ( SUM ( 'Table2'[TOTAL SAVINGS] ), ALLSELECTED ( 'Table2'[CATEGORY] ))
    )

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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