Forum Discussion
DAX Measure - Co-occurrences
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 |
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.
3 Replies
johnt75Super User
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.
Great, great, great! Exactly what i want! Thank you so much.
- 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).
