Forum Discussion

MountainView's avatar
MountainView
Frequent Visitor
3 years ago

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:

OrderProduct
A1P1
A1P2
A1P3
A1P4
A2P2
A2P3
A2P4
A2P5
A3P3
A3P4
A3P5
A4P1
A4P6
A5P5
A5P6
A6P6

 

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