Simplifying a SUMMARIZECOLUMNS with FILTERS EVALUATE query

[mbahonen]

I'm trying to simplify a DAX Query formula and hoping someone can help me. I'm still somewhat new to DAX Queries to extract data from my model and am not always sure of the syntax. I created the Sample query below to illustrate what I have created. This works and does what I want it to do, which is to give me a total value for all dates (within the defined date range) for an ID (there are many, so this is my high level group). I just want one total though and don't want to list rows of records by the date or other dimensions. As I said, the below formula seems to be doing what I want, but I have to duplicate the FILTER values in both the SUMMARIZECOLUMNS portion AND in the ADDCOLUMNS portion for my SUM value. Is there any way to simplify this formula so I only need to apply the filters one time? 

EVALUATE
ADDCOLUMNS (
    SUMMARIZECOLUMNS (
        'FactTable'[ID],
        FILTER (
                'FactTable',
                'FactTable'[ID] IN { "000012345678" }
                    && 'FactTable'[Date] >= DATE ( 2024, 6, 30 )
                    && 'FactTable'[Date] <= DATE ( 2024, 11, 30 )
                    && 'FactTable'[OtherDimension] = "True"
        )
    ),
    "TotalToSum",
        CALCULATE (
            SUM ( 'FactTable'[TotalToSum] ),
            FILTER (
                'FactTable',
                'FactTable'[ID] IN { "000012345678" }
                    && 'FactTable'[Date] >= DATE ( 2024, 6, 30 )
                    && 'FactTable'[Date] <= DATE ( 2024, 11, 30 )
                    && 'FactTable'[OtherDimension] = "True"
            )
        )
)

ETA: I just realized this formula doesn't do exactly as I want either. When I remove the ID filter, it then lists all the IDs with the same total rather than grouping the total by the ID which is what I'm trying to do. 

[OwenAuger · Accepted solution]

Hi @mbahonen 

I would recommend something like this:

EVALUATE
VAR Filter_ID =
    TREATAS ( { "000012345678" }, 'FactTable'[ID] )
VAR Filter_Date =
    DATESBETWEEN ( 'FactTable'[Date], DATE ( 2024, 6, 30 ), DATE ( 2024, 11, 30 ) )
VAR Filter_OtherDimension =
    TREATAS ( { "True" }, 'FactTable'[OtherDimension] )
RETURN
    SUMMARIZECOLUMNS (
        -- Groupby columns
        'FactTable'[ID],
        -- Filters
        Filter_ID,
        Filter_Date,
        Filter_OtherDimension,
        -- Additional columns
        "TotalToSum", SUM ( 'FactTable'[TotalToSum] )
    )

• SUMMARIZECOLUMNS itself can add columns computed in the filter context of the Groupby columns, so there is no need to wrap SUMMARIZECOLUMNS in ADDCOLUMNS in this case.
• Also, for clarity, I suggest creating variables for the filters.
• It's generally best to filter columns rather than tables, which is why I suggest splitting the filters by column. See https://www.sqlbi.com/articles/filter-columns-not-tables-in-dax/

Does the above query work as intended?

Suggested reading:

https://www.sqlbi.com/articles/introducing-summarizecolumns/

https://dax.guide/summarizecolumns/

Regards

[mbahonen]

So one question I have is this... I used the ADDCOLUMNS function in my original version on the advisement of Marco from SQLBI via this article: https://www.sqlbi.com/articles/all-the-secrets-of-summarize/

Do you have any concerns with your version without using it? 

[OwenAuger]

No concerns 🙂

The SUMMARIZECOLUMNS function is designed and optimized to handle computed columns like this. I would consider it a standard summarization function in any DAX query. Indeed, Power BI visuals generate DAX queries with this kind of syntax.

Most examples on https://dax.guide/ use a similar pattern to combine grouping columns and computed columns.

Also, until recently, SUMMARIZECOLUMNS couldn't be used reliably within ADDCOLUMNS among other scenarios.

(Some discusson here)

With the older function SUMMARIZE, ADDCOLUMNS is best practice due to some quirks mentioned in the article you linked to.

Regards

[mbahonen]

Thanks again for that extra detail! And for what its worth, so far all my versions of the adapted query have worked as I am expecting! 

[mbahonen]

Thank you! That works great! Thank you also for inlcuding the comments with the sections. Its not always easy to see where the pieces fit in from the official documentation. I appreciate your help!