Performance tuning: Compute sales values for Items and Customers NOT Selected

[lgordon]

Hello

I'm trying to optimize some performance for the measure below.
The concept is to be able to select a Customer and see the sales of similar customers, filtering items to ones the selected customer hasn't sold. Similar customers is defined as those with matching Region and Warehouse values on Customer dim.

Model:
'Customer' -> 'Sales' <- 'Item'

'Customer' is about 5k rows
'Item' is about 100k
'Sales' is about 100k

[Sales] = SUM('Sales'[SalesAmount])

Similar Customer Sales = 
var ProductList =
    EXCEPT(
        ALL('Item'),
       'Item'
    )
var CustomerList = 
    EXCEPT(
        FILTER(
        ALL('Customer'),
        'Customer'[Warehouse] = SELECTEDVALUE('Customer'[Warehouse]) 
            && 'Customer'[Region] = SELECTEDVALUE('Customer'[Region])),
        'Customer'
    )
return
CALCULATE([Sales],CustomerList,ProductList)

Is there a better way to approach this or some optimizations for this measure?

[v-stephen-msft · Accepted solution]

Hi @lgordon ,

You could try to create a new table only with Customer ID. This new table is for selecting the consumer ID for the slicer. There is no relationship with other tables.

CustomerID = VALUES(Customer[CustomerID])

Then try this measure

Similar Customer Sales =
VAR _re =
    CALCULATE (
        MAX ( 'Customer'[Region] ),
        FILTER (
            'Customer',
            'Customer'[CustomerID] = SELECTEDVALUE ( CustomerID[CustomerID] )
        )
    )
VAR _wa =
    CALCULATE (
        MAX ( 'Customer'[Warehouse] ),
        FILTER (
            'Customer',
            'Customer'[CustomerID] = SELECTEDVALUE ( CustomerID[CustomerID] )
        )
    )
VAR _ids =
    SUMMARIZE (
        FILTER (
            ALL ( 'Customer' ),
            'Customer'[Region] = _re
                && 'Customer'[Warehouse] = _wa
        ),
        [CustomerID]
    )
RETURN
    CALCULATE (
        SUM ( 'Sales'[SalesAmount] ),
        FILTER ( 'Sales', 'Sales'[CustomerID] IN _ids )
    )

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lgordon]

I made more progress, but am still not sure if this is the most optimal way of doing this.

var summary = 
    CALCULATETABLE(
        ALL(
            Sales[ItemID],
            Sales[CustomerID]
        )
    )
var selectedvalues = 
    SUMX(
        summary,
        COUNTROWS(Sales)
        )
return
IF(
    ISBLANK(selectedvalues),         
    CALCULATE(
        [Sales],
        FILTER(
            ALL('Customer'[CustomerID],'Customer'[Warehouse],'Customer'[Region]),
            NOT('Customer'[CustomerID] in VALUES('Customer'[CustomerID]))
            && 'Customer'[Warehouse] IN VALUES('Customer'[Warehouse])
            && 'Customer'[Region] IN VALUES('Customer'[Region])
        )
    ),
    BLANK()
)