Forum Discussion

KLAH96's avatar
KLAH96
Regular Visitor
5 years ago
Solved

Specific filters

Hello everyone,

I need your help for the following problem : 

At the filter level, I filter on a category and at the graph level, I display all categories except the filtered one. by using the following measurement :

mesure = CALCULATE([Σ of commitments], ALL('Category Segment'), NOT('Category Segment'[Category label]) IN FILTERS ('Category Segment'[Category label]))

 

Now I need to display only the categories of the suppliers in the filtered category

 

For example : 
supplier 1 : C1 , C2
supplier 2 : C1, C3
supplier 3 : C4, C5
I filter on C1, what I want to display is : C2, C3

 

thanks for your help

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi KLAH96 ,

     

    Try the following formula:

     

    sum = 
var FilteredID = 
    CALCULATETABLE(
        VALUES(commitment[id_suppliers]),
        ALLSELECTED(commitment)
    )
var AllID = 
    CALCULATETABLE(
        VALUES(commitment[id_suppliers]),
        ALL(commitment)
    )
return 
    CALCULATE(
        [Σ of commitments], 
        NOT('Category Segment'[Category label]) IN FILTERS ('Category Segment'[Category label]),
        INTERSECT( AllID, FilteredID )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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

     

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    KLAH96 Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

    • KLAH96's avatar
      KLAH96
      Regular Visitor
      5 years ago

      hello 

       

      Thank you for these tips.

       

      we can imagine that I have 3 tables :

      table 1 : liste of suppliers

      Suppliersid_suppliers
      S11
      S22
      S33

       

      table 2 : commitment

       

      id_supplierscategorycommitment
      1C1100
      1C2500
      2C1250
      2C3400
      3C4500
      3C5600

       

      table 3 : category segment 

       

      categoryCategory label
      C1cat 1
      C2cat 2
      C3cat 3
      C4cat 4
      C5cat 5

      if I filter on the category label  'cat 1' and display the list of categories except the filtered one, i obtain : 

       

      sum = CALCULATE([Σ of commitments], ALL('Category Segment'), NOT('Category Segment'[Category label]) IN FILTERS ('Category Segment'[Category label]))

      results :

      categorysum
      C2500
      C3400
      C4500
      C5600

       

      But what I really want to obtain is only 

      categorysum
      C2500
      C3400

      only the categories of suppliers in common with the "C1".

       

      thanks

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        5 years ago

        Hi KLAH96 ,

         

        Try the following formula:

         

        sum = 
var FilteredID = 
    CALCULATETABLE(
        VALUES(commitment[id_suppliers]),
        ALLSELECTED(commitment)
    )
var AllID = 
    CALCULATETABLE(
        VALUES(commitment[id_suppliers]),
        ALL(commitment)
    )
return 
    CALCULATE(
        [Σ of commitments], 
        NOT('Category Segment'[Category label]) IN FILTERS ('Category Segment'[Category label]),
        INTERSECT( AllID, FilteredID )
    )

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

        Best Regards,
        Winniz

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