cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mouadh_M
New Member

How to ignore a specific filter in measure and keep all other filters?

Hello, 

I am currently facing a problem with eliminating only one specific filter from a measure and keep all other possible filters.

Below an example:

 

IDProduction_LineDefectProduced quantity (meter)DateProd
511162122EXT08OK 8,494.0018/04/2022
511162993EXT01Spark 8,463.0018/04/2022
511162992EXT01Node8,463.0018/04/2022
511162128EXT05Spark 8,494.0018/04/2022
511162121EXT06Spark 4,344.0018/04/2022
511162126EXT01OK 8,494.0018/04/2022
511162124EXT02OK 4,149.0017/04/2022
511162127EXT01OK 8,494.0017/04/2023
511162033EXT01Diameter112.0017/04/2024
511162034EXT01Sparl93.0017/04/2025
511162021EXT07OK 4,990.0018/04/2022
511162023EXT07OK 4,989.0016/04/2022
511162024EXT07OK 4,628.0016/04/2023
511162020EXT07OK 4,989.0016/04/2024
511162025EXT08Node165.0016/04/2025
511162035EXT08OK INC1,122.0016/04/2026
511162028EXT02OK INC1,200.0016/04/2027
511162022EXT02OK 4,990.0018/04/2022
511162036EXT01Node725.0015/04/2022
511162032EXT05Node3,045.0015/04/2023
511162037EXT01Node70.0015/04/2024
511162031EXT01OK 3,507.0015/04/2025
511162038EXT01OK 4,995.0015/04/2026
511162039EXT01OK 4,989.0018/04/2022
511162029EXT01Spark 168.0018/04/2022


So, I want to apply this equation: sum(Production quantity for specefic selected defect) / sum(production quantity, taking into account all types of defects), also i want the equation to be succeptible to change each time i choose a specific defect on slicer.

However i want to keep all other possible filters applicable (for example: dateprod, and line).

Thank you in Advance 

 

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @Mouadh_M ,

 

If you want to get toe top defect by measure, you can try to create a rank measure and then get defect whose rank =1.

 

Rank = RANKX(CALCULATETABLE( VALUES('Table'[Defect]),ALL('Table')),[Measure],,DESC,Dense)
Top Defect = 
CALCULATE(MAX('Table'[Defect]),FILTER(VALUES('Table'[Defect]),[Rank] = 1)) 

 

Result is as below.

By Default:

RicoZhou_1-1652160219682.png

Select Production line:

RicoZhou_0-1652160211639.png

 

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
tomfox
Super User
Super User

Hi @Mouadh_M ,

 

Were you thinking of something like this? 🙂

tomfox_0-1651770843998.png

 

Here the DAX:

Measure = 
DIVIDE ( 
    CALCULATE ( SUM ( Table[Produced quantity (meter)] ) ),
    CALCULATE ( SUM ( Table[Produced quantity (meter)] ), REMOVEFILTERS ( Table) )
)

 

Note, the measure is pretty dynamic and works for other dimensions, too. Like this:

tomfox_1-1651770958164.png

This will lead to the following behaviour when dragging in another attribute:

tomfox_2-1651771498229.png

 

If you would like to have it more fixed like this (see measure2)...

tomfox_3-1651771563732.png

 

... I'd recommend the following DAX code instead:

Measure2 = 
VAR _a =CALCULATE ( SUM ( Table[Produced quantity (meter)] ), ALLEXCEPT( Table, Table[Defect] ) )
VAR _b =CALCULATE ( SUM ( Table[Produced quantity (meter)] ), ALL ( Table) )
RETURN
DIVIDE ( _a, _b )

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hello tomfox, 

I really appreciate your help, your suggestion was helpful, but i found that if i use the defect filter it gave me a better result: 

Measure = 
DIVIDE ( 
    CALCULATE ( SUM ( Table[Produced quantity (meter)] ) ),
    CALCULATE ( SUM ( Table[Produced quantity (meter)] ), REMOVEFILTERS ( Table[Defect]) )
)

can you help me figure how to plot  automatically the top defect by measure? 

thank you in advance

RicoZhou
Community Support
Community Support

Hi @Mouadh_M ,

 

If you want to get toe top defect by measure, you can try to create a rank measure and then get defect whose rank =1.

 

Rank = RANKX(CALCULATETABLE( VALUES('Table'[Defect]),ALL('Table')),[Measure],,DESC,Dense)
Top Defect = 
CALCULATE(MAX('Table'[Defect]),FILTER(VALUES('Table'[Defect]),[Rank] = 1)) 

 

Result is as below.

By Default:

RicoZhou_1-1652160219682.png

Select Production line:

RicoZhou_0-1652160211639.png

 

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors