topic Re: DAX Measure - Co-occurrences in DAX Commands and Tips

DAX Measure - Co-occurrences

Adamkowalsky92 — Wed, 11 Dec 2024 12:04:17 GMT

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

Re: DAX Measure - Co-occurrences

johnt75 — Wed, 11 Dec 2024 13:45:12 GMT

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.

Re: DAX Measure - Co-occurrences

Adamkowalsky92 — Wed, 11 Dec 2024 13:56:09 GMT

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

Re: DAX Measure - Co-occurrences

ThxAlot — Wed, 11 Dec 2024 18:41:01 GMT

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