DAX Measure filter (not in, or only in)

[Anonymous]

Hi

I have a simple datamodel with date, store, customer, product and a sales table binding them together.

I need to to do 3 mesaures that I am struggleing with.

1) The total sales of customers visiting Store A that have not bought anything in any other store.

2) The total sales of customers visiting Store B  that have not bought anything in any other store.

3) The total sales of customers visiting BOTH Store A and B

I need to measure this over the date dimension

Thanks

[v-chuncz-msft · Accepted solution]

@Anonymous,

Check the following measures.

TotalSalesA =
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        VALUES ( Sales[CustomerId] ),
        CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ) ) = 1
            && CALCULATE ( MAX ( Sales[StoreId] ) ) = 1
    )
)

TotalSalesAB =
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        VALUES ( Sales[CustomerId] ),
        CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ), Sales[StoreId] IN { 1, 2 } )
            = 2
    )
)

[Ashish_Mathur]

Hi @Anonymous,

Share a dataset and show the expected result.

[Anonymous]

Maybe try something like this?

[Only A Sales] =
CALCULATE (
    [Sales],
    FILTER (
        VALUES ( Sales[CustomerID] ),
        DISTINCTCOUNT ( Sales[StoreID] ) = 1
            && Sales[StoreID] = "A"
    )
)

You're simultaneously checking that the customer has only shopped in 1 store (distinctcount = 1) and that this store = A.

Change "A" to "B" for the 2nd measure.

For the 3rd measure, try this:

[A or B Sales] =
CALCULATE (
    [Sales],
    FILTER (
        VALUES ( Sales[CustomerID] ),
        DISTINCTCOUNT ( Sales[StoreID] ) = 2
            && Sales[StoreID] in {"A", "B"}
    )
)

[Anonymous]

Trying this out now in a simplified model.

TotalSalesA = CALCULATE(
    [Sales],
    FILTER(
        VALUES(Sales[CustomerId]),
        DISTINCTCOUNT(Sales[StoreId]) = 1 && Sales[StoreId] = 1
    )
)

Getting error:

A single value for column 'StoreId' in table Sales cannot be determined. This can happen when a measure formula refers to a column that contains many values withour specifying an aggregation.

[v-chuncz-msft · Accepted solution]

@Anonymous,

Check the following measures.

TotalSalesA =
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        VALUES ( Sales[CustomerId] ),
        CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ) ) = 1
            && CALCULATE ( MAX ( Sales[StoreId] ) ) = 1
    )
)

TotalSalesAB =
CALCULATE (
    SUM ( Sales[Sales] ),
    FILTER (
        VALUES ( Sales[CustomerId] ),
        CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ), Sales[StoreId] IN { 1, 2 } )
            = 2
    )
)

[Anonymous]

Thats it!  Thankyou so much.

[Anonymous · Accepted solution]

The TotalSalesAB had a bug if a customer visits stores 1, 2 and 3 for example. So i changed it to this:

TotalSalesAB = 
CALCULATE (
    [TotalSales];
    FILTER (
        VALUES ( Sales[CustomerId] );
        CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ) ) = 2 && CALCULATE ( DISTINCTCOUNT ( Sales[StoreId] ); Sales[StoreId] <> 1 && Sales[StoreId] <> 2 ) = 0 
    )
)

[Anonymous]

Thanks!

[v-chuncz-msft]

@Anonymous,

If your problem has been resolved, please help mark the useful reply as answer. Your contribution is highly appreciated.