Forum Discussion
Resolver IFiltering a measure doesn't work in Aggregated Context
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:
Thanks!
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]))RETURNCALCULATE([Devices], FILTER(NATURALINNERJOIN(tbl, Table1), [Percentage of Enabled Sessions] >= 0.5))
4 Replies
[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_
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]))RETURNCALCULATE([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?
