topic Re: disjoint set of products in DAX Commands and Tips

disjoint set of products

Anonymous — Wed, 29 Jun 2022 08:41:03 GMT

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

Re: disjoint set of products

amitchandak — Wed, 29 Jun 2022 10:02:35 GMT

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

Re: disjoint set of products

Anonymous — Wed, 29 Jun 2022 10:07:29 GMT

hi amitchandak ,

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

Re: disjoint set of products

amitchandak — Wed, 29 Jun 2022 10:14:58 GMT

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.

Re: disjoint set of products

Anonymous — Wed, 29 Jun 2022 14:55:23 GMT

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.

Re: disjoint set of products

v-chenwuz-msft — Mon, 04 Jul 2022 13:56:55 GMT

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.

Re: disjoint set of products

Anonymous — Mon, 04 Jul 2022 16:00:08 GMT

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

Re: disjoint set of products

Anonymous — Mon, 04 Jul 2022 16:08:06 GMT

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