Filtering a measure doesn't work in Aggregated Context

[Peter_]

Hi,

I've created a Measure which calculates another measure with an applied filter, but the filtering is incorrect in an aggregated context. Any suggestions? Note that the nested measure definition (i.e. give me [Devices] for which X is true) is a requirement for this.

I'm trying to answer following:

Given a set of devices, each with a session count and whether the sessions were "Is Enabled".

Give me a number of devices for which 50% or more sessions were Enabled.

Measure in question:

[Enabled Devices: [Devices]] + Enabled] = CALCULATE( [Devices], FILTER(Table1, [Percentage of Enabled Sessions] >= 0.5))

But this gives me all [Devices] without the filter being applied. In the below input dataset, only one device out of two should pass the check, but the above measure returns 2:

Test input:

Device	Is Enabled	Sessions
1	TRUE	3
1	TRUE	2
1	FALSE	1
1	FALSE	1
2	TRUE	1
2	FALSE	5

Measures:

Devices = CALCULATE( SUMX(SUMMARIZE(Table1,Table1[Device]),1), Table1[Device]>0)

Percentage of Enabled Sessions =

SUMX(SUMMARIZE( Table1,

Table1[Device],

"Number of Enabled Sessions", CALCULATE(SUM(Table1[Sessions]), Table1[Is Enabled] = true),

"Number of All Sessions", SUM(Table1[Sessions])),

DIVIDE([Number of Enabled Sessions], [Number of All Sessions]))

Over 0.5 Sessions being Enabled = IF([Percentage of Enabled Sessions] > 0.5, "Yes", "No")

Intermediate output:

Thanks!

[Kristjan76]

[Enabled Devices: [Devices]] + Enabled] = VAR tbl = ADDCOLUMNS( SUMMARIZE( Table1; Table1[Device]; "Number of Enabled Sessions"; CALCULATE(SUM(Table1[Sessions]); Table1[Is Enabled] = true); "Number of All Sessions"; SUM(Table1[Sessions]) ); "Enable";DIVIDE([Number of Enabled Sessions]; [Number of All Sessions]) ) RETURN COUNTROWS( FILTER( tbl; [Enable]>0,5 ) )

[Peter_]

That gives the correct count, but is it possible to do this by calling a [Devices] measure within the parent measure and not calculate it in place? In other words, can I filter the table just to devices that pass the [Percentage of Enabled Sessions ] check and hand that off to the [Devices] measure to apply any further filters? So instead of doing the commented version from Kristjan76's solution, which gives 1, to the uncommented one, which gives 2,?

[Enabled Devices: [Devices]] + Enabled 2] =

VAR tbl = ADDCOLUMNS(SUMMARIZE( Table1,

Table1[Device],

"Number of Enabled Sessions", CALCULATE(SUM(Table1[Sessions]), Table1[Is Enabled] = true),

"Number of All Sessions", SUM(Table1[Sessions])),

"Percentage of Enabled Sessions", DIVIDE([Number of Enabled Sessions], [Number of All Sessions]))

RETURN

// COUNTROWS(FILTER(tbl, [Percentage of Enabled Sessions] >= 0.5))

CALCULATE( [Devices], FILTER(tbl, [Percentage of Enabled Sessions] >= 0.5))

[Peter_ · Accepted solution]

I think I've found a solution using an inner join which gives 1 in the test sampleset. Thanks Kristjan76 for giving me a hint why it wasn't working.

[Enabled Devices: [Devices]] + Enabled 2] =

VAR tbl = ADDCOLUMNS(SUMMARIZE( Table1,

Table1[Device],

"Number of Enabled Sessions", CALCULATE(SUM(Table1[Sessions]), Table1[Is Enabled] = true),

"Number of All Sessions", SUM(Table1[Sessions])),

"Percentage of Enabled Sessions", DIVIDE([Number of Enabled Sessions], [Number of All Sessions]))

RETURN

CALCULATE([Devices], FILTER(NATURALINNERJOIN(tbl, Table1), [Percentage of Enabled Sessions] >= 0.5))

[Peter_]

Is it maybe that the parent measure is not evaluating [Percentage of Enabled Sessions] at per device granularity? Is it possible to do so with nested measure?