Creating DAX table with filters

Hi samc_26,

Thank you for providing more context and for your continued efforts in refining your DAX query. You're right with using SUMMARIZE and DISTINCTCOUNT, and it’s great to see you experimenting with different approaches. 

Based on your latest reply, it looks like the key challenge is applying multiple filter values on the same column, such as 'US' = "ACKL" or other values, along with conditions like "Yes", "N/A", etc., on other columns. The good news is that this can be done efficiently using the IN operator within a FILTER function. 

Below is a revised DAX formula that builds on your current SUMMARIZE structure and correctly applies multiple filter values: 

Table new =
SUMMARIZE(
    FILTER(
        'Table',
        'Table'[US] IN { "ACKL", "XYZ", "123" } &&
        'Table'[Column1] IN { "Yes", "N/A" } &&
        'Table'[Column2] IN { "Red", "Blue" }
    ),
    'Table'[Area],
    'Table'[Week Commencing],
    "Total orders", DISTINCTCOUNT('Table'[Order])
)

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 

Thank you.