topic Re: ignore slicer filter within topN in DAX Commands and Tips

ignore slicer filter within topN

Anonymous — Tue, 06 Apr 2021 13:15:39 GMT

I tried to make a control chart in powerbi dashboard where the central line needs to be the average of the first top 30, sort on date created. The same goes for the STDEV. The channel_1_cq is going to be the line chart in these control chart. The goal is to be able to select instrument ID and lot filter slicer and see the values of the channel_1_cq changes

However, The average and STDEV needs to fixed and not be influeced by instrument ID. But it does needs to be influenced by lot

Table name: kit

Sample ID	Lot	Instrument ID	Channel_1_cq	Date created
P1	A	C0044	29.892	24/03/2021 17:56
P2	B	C0044	29.89	24/03/2021 19:40
N3	C	C-0040	29.88	24/03/2021 21:32

calculated columns for control chart:

AVERAGETOP30 = CALCULATE( AVERAGEX(TOPN(30,'Kit','Kit'[Date created],ASC),'kit'[Channel_1_Cq]),ALLSELECTED('kit'))

STDEVXTOP30 = CALCULATE( STDEVX.S(TOPN(30,'Kit','Kit'[Date created],ASC),'kit'[Channel_1_Cq]),ALLSELECTED('kit'))

STDEVX.S

If i select machine IDs both the central line and SD changes depending on the machine i select. How can i ignore the machine ID part only for the control chart?

Re: ignore slicer filter within topN

amitchandak — Tue, 06 Apr 2021 14:39:01 GMT

Anonymous , see if removefilters can help

example

CALCULATE( STDEVX.S(TOPN(30,'Kit','Kit'[Date created],ASC),'kit'[Channel_1_Cq]),ALLSELECTED('kit'), removefilters('Kit'[MachineID]))

Re: ignore slicer filter within topN

Anonymous — Tue, 06 Apr 2021 15:56:35 GMT

Hi amitchandak,

I already tried it, but it still shifts. It seems not to work in combination with all select. I also tried ALLEXCEPT('kit'[LOT]) which seems to be able to fixed. But my average number suddently shifts from 30.02 to 29.92. and it is fixed on 29.92 which is not the right number.

Re: ignore slicer filter within topN

Anonymous — Tue, 06 Apr 2021 16:10:46 GMT

I have figure out why the number change. I forgot to mention that the top30 is only based sample ID starting with 'P'. I have a hard filter saying only shows sample ID starting with "P'. However all except still ignores this and calculate top 30 ignoring if it is an P sample or a N sample.

My calc now instead of all select is ALLEXCEPT with kit [lot] and kit [sample id]

ps: i can not post the calc, i get a html error