Nested Filter DAX Query

[robarivas]

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 DAX query using nested filtering logic (i.e., combining multiple && and ||) without having to output every field used in the filtering logic should work but I'm having a hard time finding such a pattern or examples online or even in the DAX book. It seems to be the || across more than 1 column part in particular that is causing trouble.

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

[OwenAuger]

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]

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]

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

[SpartaBI · Accepted solution]

@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]

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.

[SpartaBI]

@robarivas my bad, I forgot that you can't use logic comparisons like in calculate. 
In calcualte these translate to tables behind the scene so you can make your code more short, but in summarizcolumns you must write the explicit table filter argument.
I've changed the code in the original message. You can accept it now as a solution 🙂