Creating DAX table with filters

[samc_26]

Hi, I have a table which I was assisted in creating which uses ADDCOLUMNS, SUMMARIZE and ALLEXCEPT along with a CALCULATE added in to count the values in another column. My table has the columns 'Week commencing', 'Area' and 'Total orders'. The first two are in the summarize so I only get one row for each week for each area and then a count against these rows for total orders. 

I thought that this would give me the desired results but I've realised that there are filters on other columns which I need to add to this table to produce the correct figures but I have no idea how to integrate filters to this table...

There are several other columns I need to filter with multiple conditions such as "Yes" or "N/A'.

Can anyone help please? This is the original DAX which I need to either build on or start over with! 

New Table = ADDCOLUMNS(

    SUMMARIZE( 'Table',

        'Table'[Week Commencing],

        'Table'[Area] ),

        "Total orders", CALCULATE(DISTINCTCOUNT('Table'[Order]),

    ALLEXCEPT('Table',

        'Table'[Week Commencing],

        'Table'[Area]) ) )

Thank you for reading!

[Sonalika]

You can try this DAX:

New Table =
ADDCOLUMNS(
SUMMARIZE(
'Table',
'Table'[Week Commencing],
'Table'[Area]
),
"Total orders",
CALCULATE(
DISTINCTCOUNT('Table'[Order]),
'Table'[Column1] = "Yes",
'Table'[Column2] = "N/A",
KEEPFILTERS(VALUES('Table'[Week Commencing])),
KEEPFILTERS(VALUES('Table'[Area]))
)
)

[samc_26]

Hi @Sonalika thank you so much for the reply, I have used this formula but I'm getting the error "The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column".

I've added multiple values in the filters for some columns so not sure if that is the issue? So it looks like 

'Table'[column] = "Yes", "N/A",

'Table'[column = "Red", "Blue",

Is this the right way to write out multiple value filters? 

Thank you 

[v-saisrao-msft]

Hi @Sonalika,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Thank you @samc_26, for your insights.

It seems like the error you're encountering is related to how multiple filter conditions are being applied in your CALCULATE function. When you want to filter based on multiple values for a column, you’ll need to use logical operators (like ||) to combine the conditions, instead of listing them as separate expressions. 

When filtering on values such as "Yes" or "N/A", you can combine these conditions using || (logical OR) to ensure the correct syntax.

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. 

[samc_26]

Hi @v-saisrao-msft thank you for your help, unfortunately I'm still not getting anywhere fast! I tried to replace the ',' with || but it still didn't work. The closest I managed to get to the result I needed was using the below code (which produced a table) but I need to add more conditions to the [US] column and I don't know how to do this, can you help? Thank you

Table new =

    SUMMARIZE(

        FILTER('Table','Table'[US] = "ACKL"),

        'Table'[Area], 'Table'[Week Commencing],

"Total orders", DISTINCTCOUNT('Table'[Order]))

[v-saisrao-msft · Accepted solution]

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. 

[samc_26]

Genuis thank you! I had the same formula structure except I was using || instead of && as I had seen something previously which said to use the || but I have no idea when I should or shouldn't be using them! It works perfectly now thank you so much!