Forum Discussion

robarivas's avatar
robarivas
Post Patron
4 years ago
Solved

Nested Filter DAX Query

Would anyone be able to tell me how to fix this DAX Query?   It gives me the error: SummarizeColumns() and AddMissingItems() may not be used in this context.   The general pattern for writing a D...
DAX Query
Need Help DAX
Nested Filter
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    robarivas you should not use CALCULATETABLE on top of SUMMARIZECOLUMNS.
    You can achieve the same by using the filter parameter inside SUMMARIZECOLUMNS.
    So, you should write your code like this:

     

     

     

    SUMMARIZECOLUMNS (
    'DimTable1'[Field_A],
    'DimTable1'[Field_G],
    FILTER(
        ALL('DimTable4'[Field K]),
        'DimTable4'[Field K] = "Revenue"
    ),
    FILTER(
        ALL('DimTable1'[Field_A],'DimTable1'[Field_G]),
        'DimTable1'[Field_A] IN { "West", "East" } 
           || ( 'DimTable1'[Field_G] IN { "Red", "Blue" } && 'DimTable1'[Field_A] = "" )
    ),
    "Total Amount", [Total Amount]
)

     

     

     


     In case it solved your question, please mark this as a solution. Appreciate your Kudos

SpartaBI's avatar
SpartaBI
Community Champion
4 years ago

robarivas you should not use CALCULATETABLE on top of SUMMARIZECOLUMNS.
You can achieve the same by using the filter parameter inside SUMMARIZECOLUMNS.
So, you should write your code like this:

 

 

 

SUMMARIZECOLUMNS (
    'DimTable1'[Field_A],
    'DimTable1'[Field_G],
    FILTER(
        ALL('DimTable4'[Field K]),
        'DimTable4'[Field K] = "Revenue"
    ),
    FILTER(
        ALL('DimTable1'[Field_A],'DimTable1'[Field_G]),
        'DimTable1'[Field_A] IN { "West", "East" } 
           || ( 'DimTable1'[Field_G] IN { "Red", "Blue" } && 'DimTable1'[Field_A] = "" )
    ),
    "Total Amount", [Total Amount]
)

 

 

 


 In case it solved your question, please mark this as a solution. Appreciate your Kudos

robarivas's avatar
robarivas
Post Patron
4 years ago

Thank you SpartaBI 

 

Your code seems to solve the nested AND OR logic issue. However, it does not seem to like this line:

'DimTable4'[Field K] = "Revenue"

 It kicks back the following error: "A single value for column Field_K in table DimTable4 cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregagtion such as min, max, count, or sum to get a single result."

 

However, changing that line to  TREATAS ( { "Revenue" }, 'DimTable4'[Field_K] ) fixes the issue. Not sure I understand why.

 

So then I thought well what if I could just put that filter into the FILTER section of your code. But then it complains that the ALL function can't accept more than 1 table.