Forum Discussion
Measure to return a statistic on matched orders
Hello.
I need help to complete a measure that first sorts orders according to number of products, then finds matching products from other orders in the same table, then sorts number of matching products between main order and matching orders.
I have a fact table with orders and a dimension table with products. It needs to be a measure, not a calculated column.
Orders fact table:
| Order | Product |
| A1 | P1 |
| A1 | P2 |
| A1 | P3 |
| A1 | P4 |
| A2 | P2 |
| A2 | P3 |
| A2 | P4 |
| A2 | P5 |
| A3 | P3 |
| A3 | P4 |
| A3 | P5 |
| A4 | P1 |
| A4 | P6 |
| A5 | P5 |
| A5 | P6 |
| A6 | P6 |
So far I can calculate a count of the product that is found among all orders the most times, but that only solves a small fraction of what I need.
VAR vProduct =
MAX( 'Orders'[Product] )
VAR vSummary =
CALCULATETABLE(
SUMMARIZE(
'Orders',
'Orders'[Order],
'Orders'[Product]
),
ALL( 'Orders' )
)
RETURN
COUNTROWS(
FILTER(
vSummary,
'Orders'[Product] = vProduct
)
)
1 Reply
- foodd
Community Champion
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.