Forum Discussion

Tommyvhod's avatar
Tommyvhod
Helper II
3 years ago

All with filter

Hello all

 

I have a data table which contains process steps  ( eg 1, 5, 10, 30 etc)

I have categories for these process steps ( e.g 1, 5, 10 = A, 30,50 = B, etc. )

I have a dimensional table for these categories ( A, B, C, etc)

 

For each steps if a defect is added it receives a code which says which category ( department ) is responsible for the defect.

 

So its like:

stepcategorydefect responsible category
1AA
5AB
10BA

30

CC

30

AA

30

BB

 

I hace a formula:

Scrap by date = CALCULATE(SUMX(DISTINCT(FILTER(Data, Data[defect responsible category] in VALUES(ProductionPlan[ category]))), Data[Rejct Qty]), ALL('Dim_category'[Defect Code Department]))
 
the 3 category tables contains the same values.
If I want to filter defects for A category:
The issue is, if I dont add the ALL to the end the final result is 1 , because the category is filtered by slicer on the visual.
If I add the ALL it ignores the category filter on visual but additionally counts all the matches like B - B and C-C - result is 5.
 
What I would like to achieve is not to filter the category by the visual and filter out only the A category (3 pcs)
Something like ALL('Dim_category'[Defect Code Department])[to keep all the lines during visual filter] and FILTER defect responsible category = 'Dim_category'[Defect Code Department]
 
Thank you

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Tommyvhod ,

     

    Based on your requirements description, I can probably see what you mean, you want to implement the requirement for "not to filter the category by the visual and filter out only the A category", based on my experience and testing, if you don't want to filter the category by the visual, you can try using "Edit" in Power BI desktop interaction" feature to turn on or off the interaction of one visual with other visuals.

    Change how visuals interact in a report - Power BI | Microsoft Learn

    For your other idea of "filter out only the A category", I think you can try to use the AllExcept() function in DAX to create a measure or calculate a column, and it can achieve the effect of removing all context filters in the table except filters that have been applied to the specified columns:

    ALLEXCEPT function (DAX) - DAX | Microsoft Learn

     

    Best Regards,

    Neeko Tang

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

    • Tommyvhod's avatar
      Tommyvhod
      Helper II
      3 years ago

      Hello Anonymous 

       

      I need to filter the data with the visual filter as well. I created a new column with the same data but for the corect defect category. Should be filtered with same dimensional filter. The problem is, if I filter the category, hides all other categories and defects where A category is responsible is not showing. ( like row 3 in table above category B defect responsible category A) Or if I am adding ALL than it is not counting only the A defect responsibles but also the category / responsible category matches. ( like row 4 C/C ).

       

      I want to keep all the rows for the category  and count only the visually filtered categorie responsible  and not the matches .