What other products

[Antmkjr]

Prod	ID
A	1
C	1
D	1
A	2
E	2
D	3
E	3
B	4
D	4

From the above data,  i need to create a report , with prod as filter ,

Eg if i select A, a table visual should show me , what other products have people who purchased A also bought.

So in this case expected output when i select A as prod filter :

C

D

E

If i select D, expected output :

A

C

E

B

[CNENFRNL]

For fun only, add one more rule to your original question,

when multiple products selected, show other products in the same purchase that contains all selected products. (eg. when A/C are selected together, only purchase 1 is qualified, then D shows)

Try to wrap your head around the measure, then your level of comprehension of DAX is beyond 99.9% of users.

_ = 
VAR __sel = COUNTROWS( ALLSELECTED( PROD[Prod] ) )
// IDs containing all selected Prods
VAR __id =
    CALCULATETABLE(
        FILTER(
            VALUES( SALES[ID] ),
            CALCULATE( DISTINCTCOUNT( SALES[Prod] ) ) = __sel
        ),
        ALLEXCEPT( SALES, PROD[Prod] )
    )
RETURN
    IF(
        ISEMPTY( SALES )
            && NOT ISEMPTY( CALCULATETABLE( SALES, __id, REMOVEFILTERS( PROD[Prod] ) ) ),
        ""
    )

[Antmkjr]

Wow!Great!

Would you be so kind to add these also to the functionality:

ID	Prod	Categoory	Sum of Amount
1	A	Cloth	40
1	C	Watch	50
1	D	Cosmetics	60
2	A	Cloth	70
2	E	Shoes	80
3	D	Cosmetics	10
3	E	Shoes	20
4	B	Electronics	30
4	D	Cosmetics	40
999	B	Electronics	55
999	C	Watch	35
999	D	Cosmetics	65
999	E	Shoes	75

Need to have Amount , And distinct count of ID to the table,

Also need a category filter with the values in the table.(in this case when Prod A is selected , Category filter should show Watch, cosmetics, shoes)

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how is your desired outcome looks like, but please check the below picture and the attached pbix file.

I suggest having a disconnected slicer table like below.

ID measure: =
IF (
    HASONEVALUE ( Data[Prod] ),
    MAXX (
        FILTER (
            Data,
            Data[Prod] <> SELECTEDVALUE ( Slicer[Prod] )
                && Data[ID] IN DISTINCT ( Slicer[ID] )
        ),
        Data[ID]
    )
)

[johnt75]

I think you would need to create a separate table containing the product keys which is not connected to any tables. You could create that like

Slicer table = ALLNOBLANKROW('Table'[Product])

You could then create a measure like

Is visible in also bought =
VAR CurrentProduct =
    SELECTEDVALUE ( 'Table'[Product] )
VAR SlicerProduct =
    SELECTEDVALUE ( 'Slicer table'[Product] )
VAR BoughtInOrders =
    CALCULATETABLE (
        VALUES ( 'Table'[ID] ),
        TREATAS ( { SlicerProduct }, 'Table'[Product] )
    )
VAR OtherProducts =
    CALCULATETABLE ( VALUES ( 'Table'[Product] ), BoughtInOrders )
VAR Result =
    IF ( CurrentProduct IN EXCEPT ( OtherProducts, { SlicerProduct } ), 1 )
RETURN
    Result

and place that as a filter on the table visual, to only show when the value is 1.