Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

ignore slicer filter within topN

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 IDLotInstrument IDChannel_1_cqDate created
P1AC004429.89224/03/2021 17:56
P2BC004429.8924/03/2021 19:40
N3CC-004029.8824/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? 

 

Capture.PNG

3 REPLIES 3
Anonymous
Not applicable

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. 

Anonymous
Not applicable

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

 

 

 

 

 

amitchandak
Super User
Super User

@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]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors