Calculated Measure problem

[MP_123]

hi, i have column in my table that has specific values for months until Nov(CortanaHeavy,CortanaMedLight), and from December other values(CortanaHeavy,CortanaLight,CortanaMedium).

i want to create a pie chart with measure filtered by this column.

i have also month slicer

if month previous December is selected, there is no problem, also if only December is selected.

the problem is when selecting both November and December.

i want to to show in the report only the November values (CortanaHeavy,CortanaMedLight).

cortana heavy - need to sum all of this measure with this value (same for Nov and Dec)

Cortana Med light - need to sum CortanaMedLight(November)+CortanaLight+CortanaMedium(December)

i wrote this measure: but for some reason it  shows only the CortanaHeavy Value

Views = if(And(month(min(Query1[Date]))<12,month(max(Query1[Date]))>11),IF(HASONEVALUE(Query1[Engagement Cohort]),
if(values(Query1[Engagement Cohort])="CortanaHeavy",sum(Query1[CardViewed]),IF(HASONEVALUE(Query1[Engagement Cohort]),if(values(Query1[Engagement Cohort])="CortanaLight",0,if(values(Query1[Engagement Cohort])="CortanaMedium",0,CALCULATE(sum(Query1[CardViewed]),filter(all(Query1[Engagement Cohort]),Query1[Engagement Cohort]="CortanaMedLight"))+CALCULATE(sum(Query1[CardViewed]),filter(all(Query1[Engagement Cohort]),Query1[Engagement Cohort]="CortanaMedium"))+CALCULATE(sum(Query1[CardViewed]),filter(all(Query1[Engagement Cohort]),Query1[Engagement Cohort]="CortanaLight"))))))))

it's a bit messy because i tried different ways to make it work 😞

attached image to see that there are values to sum, and the measure that calculated for the first only

thanks!

[v-huizhn-msft]

Hi @MP_123,

You’d better create a column which recognize the Engagement Cohort and Month, then create a measure to calculate the result. I try to reproduce your scenario using the following sample data and get expected result.
 


First create month and record columns using the formulas below.

Month = MONTH(Test3[Date])

Recard = IF(Test3[Month]=12,IF(Test3[Engagement Cohort ]="CortanaMedLight",CALCULATE(Test3[CardView1],FILTER(Test3,AND(Test3[Engagement Cohort ]="CortanaMedLight",Test3[Month]=12)))+CALCULATE(Test3[CardView1],FILTER(Test3,AND(Test3[Engagement Cohort ]="CortanaLight",Test3[Month]=12)))+CALCULATE(Test3[CardView1],FILTER(Test3,AND(Test3[Engagement Cohort ]="CortanaMedium",Test3[Month]=12))),IF(Test3[Engagement Cohort ]="CortanaHeavy",1,0)),1)

 

Then create two measures as follows.

CardView1 = COUNTA(Test3[Engagement Cohort])

View = SUM(Test3[Recard])

Finally, create a table and slicer including the month. When you select the December, it display the first result, when you select the November and  December, it still return the desired result.


 
 

If you have any other issue, please feel free to ask.

Best Regards,
Angelia

[MP_123]

@v-huizhn-msft

hi Angelia,

thank you very much for your help!

i forgot to mention that the report's data source is on direct query ( can't change it since the table is very big)

and this calculated column can't be added in direct query mode 😞

thanks!

[v-huizhn-msft]

Hi @MP_123,

Do you mean you don't create calculated column in Direct Query model? As I tested it, I can create calculated column in Direct Query model as follows. Please note the highlithed in red wire frame.



Best Regards,
Angelia

[MP_123]

yes it's possible to add new column, but can't use the calculate and filter functions.

[v-huizhn-msft]

Hi @MP_123,

Got it, I will reproduce it on my local computer, and post the update here. Thanks for understanding.

Best Regards,
Angelia

[MP_123]

thank you!