topic Re: Simplifying a SUMMARIZECOLUMNS with FILTERS EVALUATE query in DAX Commands and Tips

Simplifying a SUMMARIZECOLUMNS with FILTERS EVALUATE query

mbahonen — Thu, 05 Dec 2024 17:23:04 GMT

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.

Re: Simplifying a SUMMARIZECOLUMNS with FILTERS EVALUATE query

OwenAuger — Thu, 05 Dec 2024 18:17:41 GMT

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?

Re: Simplifying a SUMMARIZECOLUMNS with FILTERS EVALUATE query

mbahonen — Thu, 05 Dec 2024 19:42:47 GMT

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!

Re: Simplifying a SUMMARIZECOLUMNS with FILTERS EVALUATE query

mbahonen — Thu, 05 Dec 2024 21:37:07 GMT

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?

Re: Simplifying a SUMMARIZECOLUMNS with FILTERS EVALUATE query

OwenAuger — Thu, 05 Dec 2024 22:17:03 GMT

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

Re: Simplifying a SUMMARIZECOLUMNS with FILTERS EVALUATE query

mbahonen — Thu, 05 Dec 2024 22:53:46 GMT

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!