Forum Discussion
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.
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]))
)
)
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
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.
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]))
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.