Discussion Re: DAX Question in Excel
https://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3508342#M150593
Sorry I took long to trail this. I get this error message,<BR />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.'.<BR /><BR />Data_AO is your Table 1.<BR /><BR />This is what I tried before which didn't workWed, 15 Jun 2022 01:31:16 GMTImi_Jay2022-06-15T01:31:16ZDAX Question
https://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3474425#M149543
<P>I have a pivot table created through the data model. I am using measures on the pivot as columns.<BR />Actual and Active are measures.<BR />I want to calculate the total of Actuals where Active >0.<BR />What DAX formula to use ? I couldn't use calculate as filters didn't take >0 as a filter. Thanks in advance!<BR /><BR /></P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Imi_Jay_0-1654569174940.png" style="width: 400px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/377989i16107AA912042BA7/image-size/medium?v=v2&px=400" role="button" title="Imi_Jay_0-1654569174940.png" alt="Imi_Jay_0-1654569174940.png" /></span></P><P> </P>Tue, 07 Jun 2022 03:02:49 GMThttps://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3474425#M149543Imi_Jay2022-06-07T03:02:49ZRe: DAX Question
https://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3475048#M149553
<P><LI-USER uid="1289411"></LI-USER> Like this perhaps?</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="Riny_van_Eekelen_0-1654581436675.png" style="width: 453px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/378059iF5C4E4D80445EEA6/image-dimensions/453x246?v=v2" width="453" height="246" role="button" title="Riny_van_Eekelen_0-1654581436675.png" alt="Riny_van_Eekelen_0-1654581436675.png" /></span></P><P>Measure used:</P><P> </P><LI-CODE lang="excel-formula">=CALCULATE(SUMX(Table1,Table1[Actual]),Table1[Active]>0)</LI-CODE><P> </P><P> </P><P><!-- StartFragment --><SPAN class="">=</SPAN><SPAN class="">CALCULATE</SPAN><SPAN class="">(</SPAN><SPAN class="">sumx</SPAN><SPAN class="">(Table1,Table1[Actual]),Table1[Active]>0)</SPAN><!-- EndFragment --></P>Tue, 07 Jun 2022 05:59:08 GMThttps://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3475048#M149553Riny_van_Eekelen2022-06-07T05:59:08ZRe: DAX Question
https://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3484761#M149880
<P><LI-USER uid="1289411"></LI-USER> </P>
<P>For such model</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 494px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/378773iA13F6576CD8CD96C/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>assuming you [Actual] and [Active] measures are like</P>
<LI-CODE lang="excel-formula">Actual:=SUM( Table1[Actual value] )
Active:=SUM( Table1[Active value] )</LI-CODE>
<P>Actual with totals for Active only could be</P>
<LI-CODE lang="excel-formula">Actual Active :=
IF (
HASONEVALUE ( Table1[Active value] ),
[Actual],
CALCULATE ( [Actual], Table1[Active value] > 0 )
)
</LI-CODE>Wed, 08 Jun 2022 21:43:31 GMThttps://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3484761#M149880Sergei Baklan2022-06-08T21:43:31ZRe: DAX Question
https://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3508342#M150593
Sorry I took long to trail this. I get this error message,<BR />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.'.<BR /><BR />Data_AO is your Table 1.<BR /><BR />This is what I tried before which didn't workWed, 15 Jun 2022 01:31:16 GMThttps://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3508342#M150593Imi_Jay2022-06-15T01:31:16ZRe: DAX Question
https://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3508350#M150594
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.<BR /><BR />Hence my measure are as follows,<BR />Measure "Active" = CALCULATE(COUNT(Data_AO[Student]),Data_AO[Status]="Active")<BR />Measure "Complete" = CALCULATE(COUNT(Data_AO[Student]),Data_AO[Status]="Complete")<BR /><BR />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!Wed, 15 Jun 2022 01:39:10 GMThttps://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3508350#M150594Imi_Jay2022-06-15T01:39:10ZRe: DAX Question
https://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3512913#M150714
<P><LI-USER uid="1289411"></LI-USER> </P>
<P>And what is Actual - is that column or measure and how do you calculate it?</P>Wed, 15 Jun 2022 21:27:51 GMThttps://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3512913#M150714Sergei Baklan2022-06-15T21:27:51ZRe: DAX Question
https://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3519003#M150874
Actual is also a measure ,<BR />Actual = Measure "Active" + Measure "Complete".<BR /><BR />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.Fri, 17 Jun 2022 01:49:49 GMThttps://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3519003#M150874Imi_Jay2022-06-17T01:49:49ZRe: DAX Question
https://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3522659#M151000
<P><LI-USER uid="1289411"></LI-USER> </P>
<P>Perhaps like this</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 689px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/381536i021FB7047CA272D1/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>where</P>
<LI-CODE lang="excel-formula"># 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 )
)
)
</LI-CODE>Fri, 17 Jun 2022 23:17:37 GMThttps://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3522659#M151000Sergei Baklan2022-06-17T23:17:37ZRe: DAX Question
https://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3531146#M151224
Perfect, it worked although I might need to sit and understand the formulas.Tue, 21 Jun 2022 00:48:41 GMThttps://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3531146#M151224Imi_Jay2022-06-21T00:48:41ZRe: DAX Question
https://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3532143#M151261
<P><LI-USER uid="1289411"></LI-USER> , glad to help. You may play with DAX Studio for better understanding of how formula works, e.g. evaluate SUMMARIZE only, etc.</P>Tue, 21 Jun 2022 09:02:04 GMThttps://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3532143#M151261Sergei Baklan2022-06-21T09:02:04ZRe: DAX Question
https://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3541159#M151486
Thanks will doThu, 23 Jun 2022 02:37:55 GMThttps://techcommunity.microsoft.com/t5/excel/dax-question/m-p/3541159#M151486Imi_Jay2022-06-23T02:37:55Z