Specific filters

[KLAH96]

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

[Greg_Deckler]

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

hello 

Thank you for these tips.

we can imagine that I have 3 tables :

table 1 : liste of suppliers

Suppliers	id_suppliers
S1	1
S2	2
S3	3

table 2 : commitment

id_suppliers	category	commitment
1	C1	100
1	C2	500
2	C1	250
2	C3	400
3	C4	500
3	C5	600

table 3 : category segment 

category	Category label
C1	cat 1
C2	cat 2
C3	cat 3
C4	cat 4
C5	cat 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 :

category	sum
C2	500
C3	400
C4	500
C5	600

But what I really want to obtain is only 

category	sum
C2	500
C3	400

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

thanks

[v-kkf-msft · Accepted solution]

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.

[KLAH96]

Thanks a lot.

it works, very well!!