disjoint set of products

[Anonymous]

i have the data that contains several types of product and their customers. a product is bought by a couple of customer. i want to see difference bought products between two customers. simple i create two slicer one of them is for selected customer another one is excluded. i manage exclude that is only in excluded but i want to exclude product data that are intersect of selected customer and excluded customer. as you can see in below i create a measure ,but it does not work

EXCLUDE? =

VAR SELECTED=

ALL(FIRST SELECTION TABLE [CUSTOMER COLUMN])

VAR EXCLUDE= ALLSELECTED(SECOND SELECTION TABLE [CUSTOMER COLUMN])

VAR INTERSECT=INTERSECT(SELECTED,EXCLUDE)

VAR RESULT= EXCEPT(SELECTED,INTERSECT)

RETURN

RESULT

[amitchandak]

@Anonymous , this will give a table

EXCLUDE? =
VAR SELECTED=
ALL(FIRST SELECTION TABLE [CUSTOMER COLUMN])
VAR EXCLUDE= ALLSELECTED(SECOND SELECTION TABLE [CUSTOMER COLUMN])
VAR RESULT= EXCEPT(SELECTED,INTERSECT)
RETURN
RESULT

You can use that in a measure

EXCLUDE? =
VAR SELECTED=
ALL(FIRST SELECTION TABLE [CUSTOMER COLUMN])
VAR EXCLUDE= ALLSELECTED(SECOND SELECTION TABLE [CUSTOMER COLUMN])
VAR RESULT= EXCEPT(SELECTED,INTERSECT)
RETURN
calculate( Sum(Table[Value]), Filter(Table, Table[CUSTOMER COLUMN] in result) , Values(Table[CUSTOMER COLUMN]) )

[Anonymous]

hi @amitchandak ,

In solution, you mentioned that Sum(Table[Value] phrase but i need list of products(their type is string)

[amitchandak]

@Anonymous , we create a measure and then put that in visual with some group by display data of that. So have this measure in visual(You can event count rows or products)  with product

Also, check - Need of an Independent Table in Power BI - Exclude selected value : https://youtu.be/lOEW-YUrAbE

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak i have tried your solution but i need more disjoint set of product between two brand. i create dumb report for make it more clear. i look forward your ideas. 

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Please try this measure in filter pane and set it show items which is 1:

Measure =
VAR _select =
    VALUES ( 'first slicer'[customer] )
VAR _component =
    CALCULATETABLE (
        VALUES ( 'Table'[customer] ),
        ALLEXCEPT ( 'Table', 'Table'[component] )
    )
VAR _compare =
    COUNTROWS ( EXCEPT ( _select, _component ) )
RETURN
    IF ( ISFILTERED ( 'first slicer'[customer] ), IF ( _compare > 0, 1 ), 1 )

screenshot of filter pane:

And  result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[Anonymous]

Comparison =

VAR _select =VALUES ( 'first slicer'[customer])

VAR _component = CALCULATETABLE(VALUES('Table'[customer]),ALLEXCEPT('Table','Table'[component]))

VAR _exclude= VALUES( 'second slicer'[customer])

VAR _compare = COUNTROWS ( EXCEPT ( _select, _exclude ) )

RETURN

IF ( ISFILTERED ( 'first slicer'[customer]), IF ( _compare > 0, 1 ), 1 )

i have tried change your measure but i can not reach correct solution

[Anonymous]

hi @v-chenwuz-msft  thank you your effort but it is my bad that i explain my problem wrong. i have tried your measure and it worked properly but i want to listed all products that belong brand 1(from first selection) not belong brand 2(from second selection) at the same time. for example if i select brand 1 in first selection i should products that are from brand 2 but i need 2 slicer which is listed all components that belong and the other one is if it is common for 2 brands than it should remove components but keep not common components between 2 brands