Forum Discussion

samc_26's avatar
samc_26
Helper IV
1 year ago
Solved

Creating DAX table with filters

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 'Wee...
Need Help
  • v-saisrao-msft's avatar
    v-saisrao-msft
    1 year ago

    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's avatar
samc_26
Helper IV
1 year ago

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's avatar
v-saisrao-msft
Community Support
1 year ago

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's avatar
    samc_26
    Helper IV
    1 year ago

    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!