Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Alienvolm
Helper IV
Helper IV

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

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.

 

Devices.png

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

1 ACCEPTED SOLUTION

Hi @Alienvolm ,

 

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

View solution in original post

4 REPLIES 4
Fowmy
Super User
Super User

@Alienvolm 

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

https://dax.guide/selectcolumns/


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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

Hi @Alienvolm ,

 

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

Hi @v-deddai1-msft,

 

Thank you! 

 

That worked! 🙂

 

Alienvolm_0-1626451727918.png

~Alienvolm

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.