Forum Discussion

Imi_Jay's avatar
Imi_Jay
Copper Contributor
Jun 07, 2022
Solved

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...
BI & Data Analysis
  • 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 )
    )
)
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jun 07, 2022

Imi_Jay Like this perhaps?

Measure used:

 

=CALCULATE(SUMX(Table1,Table1[Actual]),Table1[Active]>0)

 

 

=CALCULATE(sumx(Table1,Table1[Actual]),Table1[Active]>0)

Imi_Jay's avatar
Imi_Jay
Copper Contributor
Jun 15, 2022
Sorry I took long to trail this. I get this error message,
This formula is invalid or incomplete: 'Calculation error in measure 'Data_AO'[Current enrolment]: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.'.

Data_AO is your Table 1.

This is what I tried before which didn't work

Resources