Forum Discussion

Imi_Jay's avatar
Imi_Jay
Brass Contributor
Jun 07, 2022

DAX Question

I have a pivot table created through the data model. I am using measures on the pivot as columns. Actual and Active are measures. I want to calculate the total of Actuals where Active >0. What DAX...
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 17, 2022

    Imi_Jay 

    Perhaps like this

    where

    # of Active :=
    CALCULATE (
        COUNT ( Data_AO[Student] ),
        Data_AO[Status] = "Active"
    )
    
    ////////////
    # of Complete :=
    CALCULATE (
        COUNT ( Data_AO[Student] ),
        Data_AO[Status] = "Complete"
    )
    
    ///////////
    # of Actual :=
    [# of Active] + [# of Complete]
    
    //////////
    # of Active Actual :=
    IF (
        HASONEVALUE ( Data_AO[Group No] ),
        [# of Actual],
        SUMX (
            SUMMARIZE (
                Data_AO,
                Data_AO[Group No],
                "actives", CALCULATE ( COUNTROWS ( Data_AO ), Data_AO[Status] = "Active" ),
                "completed", CALCULATE ( COUNTROWS ( Data_AO ), Data_AO[Status] = "Complete" )
            ),
            IF ( [actives], [actives] + [completed], 0 )
        )
    )
    
    

Resources