SOLVED

# DAX Question

Brass 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 formula to use ? I couldn't use calculate as filters didn't take >0 as a filter. Thanks in advance! 10 Replies

# Re: DAX Question

@Imi_Jay Like this perhaps? Measure used:

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

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

# Re: DAX Question

For such model assuming you [Actual] and [Active] measures are like

``````Actual:=SUM( Table1[Actual value] )
Active:=SUM( Table1[Active value] )``````

Actual with totals for Active only could be

``````Actual Active :=
IF (
HASONEVALUE ( Table1[Active value] ),
[Actual],
CALCULATE ( [Actual], Table1[Active value] > 0 )
)
``````

# Re: DAX Question

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.'.

This is what I tried before which didn't work

# Re: DAX Question

My measure are different to what you have produced. Apologies I didn't explain the data set well. In the data set there is a column "Status" which has "active and completed as status.

Hence my measure are as follows,
Measure "Active" = CALCULATE(COUNT(Data_AO[Student]),Data_AO[Status]="Active")
Measure "Complete" = CALCULATE(COUNT(Data_AO[Student]),Data_AO[Status]="Complete")

There is no column as such "Active" or complete. In that case how can I write the formula. That's where I'm stuck. Thanks for the help!

# Re: DAX Question

And what is Actual - is that column or measure and how do you calculate it?

# Re: DAX Question

Actual is also a measure ,
Actual = Measure "Active" + Measure "Complete".

Business scenario here is , this data set includes prehistorical data. Not always a group has active students in it. My aim is to aggregate the Actuals when the Active column / measure has a value.
best response confirmed by Imi_Jay (Brass Contributor)
Solution

# Re: DAX Question

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 )
)
)

``````

# Re: DAX Question

Perfect, it worked although I might need to sit and understand the formulas.

# Re: DAX Question

@Imi_Jay , glad to help. You may play with DAX Studio for better understanding of how formula works, e.g. evaluate SUMMARIZE only, etc.

Thanks will do