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

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

Hi robarivas 

That's interesting. SUMMARIZECOLUMNS can be called within CALCULATETABLE, but, as you've mentioned, an error is returned when CALCULATETABLE has a filter argument containing an || operator between conditions on different columns of the same table. This also seems to be the case if the offending filter argument is constructed as a table.

 

As an alternative, I would recommend making use of FilterTable arguments within SUMMARIZECOLUMNS itself, unless there's any reason not to. It does mean that you have to construct all filters as tables rather than use boolean expressions.

 

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

 Does this work as intended?

Regards,

Owen

  • 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