How to apply page level filters at aggregated data

[Anonymous]

Hi,

I have data which looke something like this:

L1	L2	L3	Score	Respones
A	A1	A11	8	22
A	A1	A11	6	44
A	A1	A12	10	38
A	A2	A21	1	16
A	A2	A21	5	35
A	A2	A23	0	31
A	A3	A31	8	45
A	A3	A32	1	47
A	A3	A32	2	16
A	A3	A33	10	29
B	B1	B11	8	49
B	B1	B11	5	23
B	B1	B12	5	23
B	B1	B12	6	26
B	B2	B21	2	48
B	B2	B22	3	32
B	B2	B22	10	10
B	B3	B31	7	33
B	B3	B32	2	49
B	B3	B32	5	33
C	C1	C11	2	41
C	C1	C11	8	26
C	C1	C12	5	41
C	C2	C21	7	48
C	C2	C21	5	13
C	C2	C22	0	10
C	C2	C22	6	46
C	C3	C31	5	23
C	C3	C31	4	39
C	C3	C31	7	13

Now, I am representing the data in a matrix visual in power bi where L1, L2 and L3 columns are in rows in drill down manner and Score and responses are coming as columns. 

L1, L2 and L3 columns are showed as drilled down (L3 being aggreagted to L2 being aggregated to L1).

For the responses column, my data is getting summed up while aggregating and I have the same column in page level filter as well.

Now, let's say that the data at aggregated column looks like this:

L1	Score	Responses
A	5	323
B	5.3	326
C	5.4	300

the problem that I am facing is when I want to apply response filter at this level (let's say to filter out responses <300),

my filter is working at the L3 level (or the lowest level) and removing those records which have low responses at L3 level. Whereas I want it to remove the records at the L1 level (in this case if I apply responses<300, I should only see A and B rows.)

Is it achievable through a DAX query or doing something in Power BI so that the filters act on the level at which my visualization is currently and not at the base level.

Thanks for helping

Regards

@Anonymous @Cmcmahan @parry2k @dax @MatthewR 

[helassal · Accepted solution]

Hi @Anonymous ,

Check this out:

I first created a slicer based on a calculated table that generates a column populated with numbers from 1 to 1000:

Range = GENERATESERIES(0,1000,1)

And then created a measure that first aggregates the response for each group in a visual and then compare the aggregation value to the minimum and maximum values of the slicer:

m_SumOfResponses =
var r = sum('Table'[Respones])

Return
if(r >= min(Range[Value]) && r <= max(Range[Value]),r)

Here's the sample file for reference: Sample Power BI File 

Please let me know if this solves your problem.

[Anonymous]

Hey @helassal ,

Thanks for your response. 

The method you suggested was working fine for me.

One thing that I added here is that in your case, the row will only vanish if there is only one column in the data, but if there are other columns as well, then it will not remove the entire row.

To work on that, I applied the same condition on all the measures that are pulled as columns and it was working fine then

Eg:

var r = sum('Table'[Respones])

Var temp = sumx(table1, score*responses)/sum(responses)

Return
if(r >= min(Range[Value]) && r <= max(Range[Value]), temp)

Thanks for the help!