Forum Discussion
Imi_Jay
Jun 07, 2022Copper Contributor
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...
- Jun 17, 2022
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
Jun 07, 2022Platinum Contributor
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
Jun 15, 2022Copper Contributor
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
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