topic Re: How to exclude one field from summarize columns but still include it in the query in Report Server

How to exclude one field from summarize columns but still include it in the query

Anonymous — Tue, 13 Jul 2021 19:04:35 GMT

Hi,

I had to introduce the field [PlannedMigrationDay] in my query for the paginated report for the sole purpose of filtering the paginated report.

This is the query:

/* START QUERY BUILDER */ EVALUATE SUMMARIZECOLUMNS( UserResponse[Batch], UserResponse[Response], UserResponse[PlannedMigrationDay], KEEPFILTERS( TREATAS( {"2"}, UserResponse[TenantID] )), KEEPFILTERS( TREATAS( {"4"}, UserResponse[QuestionID] )), "Responses", [Responses], "Responses %", [Responses %], "T-14", [T-14] ) /* END QUERY BUILDER */

Now I have one problem: when I have two different migration dates, my results are summarized by the date of migration whereas before they were aggregated by distinct values.

I don't want my results to be aggregated by [PlannedMigrationDay]. How can I modify the query to avoid that, but still have that field in the dataset of my paginated report?

Thanks in advance for the help!

~Alienvolm

Re: How to exclude one field from summarize columns but still include it in the query

Fowmy — Tue, 13 Jul 2021 19:42:43 GMT

Anonymous

Use SELECTCOLUMNS table function to get only the columns that you need.

https://dax.guide/selectcolumns/

Re: How to exclude one field from summarize columns but still include it in the query

Anonymous — Tue, 13 Jul 2021 20:35:01 GMT

Hi Fowmy,

I can't figure outr for the life of me how to make SELECTCOLUMNS work together with SUMMARIZECOLUMNS. I tried all combinations and the query just doesn't work.

With SELECTCOLUMNS only it works, but I see the single rows of the table, which is not what I need. I need all the fields summarized but not by UserResponse[PlannedMigrationDay].

I'm not an expert with DAX Queries, so any additional help would be really appreciated!

~Alienvolm

Re: How to exclude one field from summarize columns but still include it in the query

v-deddai1-msft — Fri, 16 Jul 2021 06:21:41 GMT

Hi Anonymous ,

You don't want your value to be summarized by PlannedMigrationDay column , by you still want to show it in the output table, am I right?

You need also a aggregation function to your PlannedMigrationDay column like max or min, please try :

EVALUATE ADDCOLUMNS(SUMMARIZECOLUMNS( UserResponse[Batch], UserResponse[Response], KEEPFILTERS( TREATAS( {"2"}, UserResponse[TenantID] )), KEEPFILTERS( TREATAS( {"4"}, UserResponse[QuestionID] )), "Responses", [Responses], "Responses %", [Responses %], "T-14", [T-14] ),"Date",CALCULATE(MAX(UserResponse[PlannedMigrationDay]),FILTER(UserResponse,UserResponse[Batch] = EARLIER(UserResponse[Batch])&&UserResponse[Response] = EARLIER(UserResponse[Response])))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Re: How to exclude one field from summarize columns but still include it in the query

Anonymous — Fri, 16 Jul 2021 16:09:20 GMT

Hi v-deddai1-msft,

Thank you!

That worked! 🙂

~Alienvolm