Forum Discussion

robarivas's avatar
robarivas
Icon for Post Patron rankPost 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

robarivas's avatar
robarivas
Icon for Post Patron rankPost Patron
4 years ago

Hello OwenAuger Thanks so much. This does work.

 

As a followup, what if my Nested Filter needed to involve more than just 1 table? Would I have to do something like this: CROSSJOIN ( ALL(Table1), ALL(Table2)) ? And is that the best way to go?

 

Looking for as flexible and general a pattern as possible.

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
4 years ago

Glad to hear it 🙂

If you're looking for a general pattern that can cover any condition involving columns of multiple tables, then yes, it would involve FILTER/CROSSJOIN/ALL.

I would recommend restricting the arguments of ALL to just the required columns though.

For example:

FILTER (
    CROSSJOIN (
        ALL ( Table1[Col1], Table1[Col2] ),
        ALL ( Table2[Col3], Table2[Col4], Table2[Col5] ),
        ...
    ),
    <condition>
)

 

In particular situations, there would likely be more efficient ways of generating filter tables, using GENERATE and TREATAS for example.

 

Regards,

Owen