Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
crnswengineer
Frequent Visitor

disjoint set of products

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
1 ACCEPTED SOLUTION

Hi @crnswengineer ,

 

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:

vchenwuzmsft_0-1656942987496.png

And  result:

vchenwuzmsft_1-1656943002255.png

 

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.

 

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

@crnswengineer , 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]) )

hi @amitchandak ,

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

@crnswengineer , 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.

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

crnswengineer_0-1656513945599.png

 



Hi @crnswengineer ,

 

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:

vchenwuzmsft_0-1656942987496.png

And  result:

vchenwuzmsft_1-1656943002255.png

 

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.

 

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

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.