Forum Discussion
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 '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!
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.
6 Replies
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.
