DAX Measure - Co-occurrences

[Adamkowalsky92]

Hi, I'm having trouble coming up with a measurement.

Simple table, 2 columns, Invoice No. and Product. The task is to obtain the result of co-occurrences on one invoice.
I want to have a slicer with product selection and a table where I add a product column to the rows and this measure to the value. Selecting product 1 in the slicer will show me the remaining products and the number of co-occurrences on one of the invoices.
for example: let's assume that product 1 appeared on 100 invoices, it will show me that product 2 appeared 50 times out of these 100 invoices, product 3 20 times etc.


Example:

Invoice no.	Product
123	1
123	2
123	3
234	1
234	3
345	1
345	4
345	5
456	1
456	2
456	3
456	4

Results, after choose Product 1 from slicer

Product	Occurreces
2	2
3	3
4	2
5	1

[ThxAlot]

It's solved already when I saw it; but just for fun only, I provide a more sophisticated solution applicable to a more generic model. What's more an intricacy of it is that when more products selected in the slicer (ie, product 1&2), only those invoices (invoice 123 & 456) containing all selected products will be further investigated for co-existing products (product 3&4).

[johnt75 · Accepted solution]

You'd need to create a separate table for use in the slicer. You can use

Product for slicer = DISTINCT( 'Table'[Product] )

Do not connect that to the main table, just use it in the slicer.

You can then create a measure like

[Num co-occurences] = IF(
SELECTEDVALUE('Product for slicer'[Product]) <> SELECTEDVALUE('Table'[Product]),
VAR InvoicesForChosenProduct = CALCULATETABLE(
	VALUES('Table'[Invoice no.]),
	TREATAS(
		VALUES('Product for slicer'[Product]),
		'Table'[Product]
	)
)
VAR InvoicesForCurrentProduct = VALUES('Table'[Invoice no.])
VAR Result = COUNTROWS(INTERSECT(
	InvoicesForChosenProduct,
	InvoicesForCurrentProduct
))
RETURN
	Result
)

and use the [Product] column from the main table in your visual.

[Adamkowalsky92]

Great, great, great! Exactly what i want! Thank you so much.