DAX customer + Product

DAX DAX Calculations
1

Good day team,

please can you help me with a dax, i think it will use a Variable but not sure.

what i am trying to achieve is to calculate sales for certain customers and certain products:

customers :

image

Products:

image

Mastering DAX.pbix (428.9 KB)

2

as with so many other issues, the answer is ‘it depends’ :slight_smile:
if your actual model is as simple as: first 10 items on the customer table, and first 7 on the products table, you could go with something like this:

Trial Measure 1 = 
    CALCULATE( [Total Sales], 
        FILTER( Customer, Customer[Customer Index] >= 1 && Customer[Customer Index] <= 10 ),
        FILTER( Products, Products[Index] >= 1 && Products[Index] <= 7))

if your products are scattered around your tables, then I recommend a filter like this:

Trial Measure 2 = 
    CALCULATE( [Total Sales], 
        Customer[Customer Index] IN {1, 2, 3, 4, 5, 6, 7, 8, 9, 10},
        Products[Index] IN {1, 2, 3, 4, 5, 6, 7})

and finally, the option that I would recommend if you were using this logic a LOT in your report, is to add a simple True/False column to your customer and product tables, to use as filters:

Trial Measure 3 = 
    CALCULATE( [Total Sales], 
        Customer[Customer Filter] = TRUE(), 
        Products[Product Filter] = TRUE() )

image
solution file is attached:
eDNA Solution - Mastering DAX.pbix (432.3 KB)

2 Likes
3

Thanks so much @Heather this worked perfectly. Based on model, trial measure 2 worked the best!

Neilon

1 Like
4

I thought the 2nd might be the most flexible option :slight_smile:
glad to know this was solved, thank you!