Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Nested CALCULATE

 

Team,

How do I count only"Met" from the below DAX results?

=IF( CALCULATE(sum('Data_C3 Level'[Vol L15]), 'Data_C3 Level'[Plan/Act]= "2022 Act") - CALCULATE(sum('Data_C3 Level'[Vol L15]), 'Data_C3 Level'[Plan/Act]= "2022 Plan")<0,"Not Met","Met")

 

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    3 years ago

    Hello Anonymous,

     

    Can you please try: 

    Met Count =
COUNTROWS(
    FILTER(
        ALL('Data_C3 Level'),
        IF(
            CALCULATE(SUM('Data_C3 Level'[Vol L15]), 'Data_C3 Level'[Plan/Act] = "2022 Act") -
            CALCULATE(SUM('Data_C3 Level'[Vol L15]), 'Data_C3 Level'[Plan/Act] = "2022 Plan") < 0,
            "Not Met",
            "Met"
        ) = "Met"
    )
)

     

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    3 years ago

    Hello Anonymous,

     

    Can you please try: 

    Met Count =
COUNTROWS(
    FILTER(
        ALL('Data_C3 Level'),
        IF(
            CALCULATE(SUM('Data_C3 Level'[Vol L15]), 'Data_C3 Level'[Plan/Act] = "2022 Act") -
            CALCULATE(SUM('Data_C3 Level'[Vol L15]), 'Data_C3 Level'[Plan/Act] = "2022 Plan") < 0,
            "Not Met",
            "Met"
        ) = "Met"
    )
)

     

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi Anonymous 
    If you are slicing by multiple columns then you need to iterate over SUMMARIZE instead of VALUES

    =
SUMX (
    VALUES ( 'Data_C3 Level'[The Slicing By Column] ),
    INT (
        CALCULATE (
            SUM ( 'Data_C3 Level'[Vol L15] ),
            'Data_C3 Level'[Plan/Act] = "2022 Act"
        )
            - CALCULATE (
                SUM ( 'Data_C3 Level'[Vol L15] ),
                'Data_C3 Level'[Plan/Act] = "2022 Plan"
            ) < 0
    )
)