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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

SummarizeColumns want to do group by & filter on columns which are used inside measure columns

SummarizeColumns want to do group by & filter on columns which are used inside measure columns

 

So what I mean 

I have Calendar, Product, ProductSales, Customer, Country, ChurnRetainSummary,  LifeTime  etc..

In LifeTime I have one column as LifeTimeMonth (month since customer created till deactivated so if Customer Created in Jan 2020 and deactivated in Dec 2020 he would be considerd for calculation in all lifetime months between Jan to Dec 2020) of a customer and has measures summarised already on MMMYYYY and LifeTimeMonth. Mesures are like Retained Customer,  Churned Customer, ChurnRate, Revenue calculated on MMMYYYY, LifeTime month

Measures are using table ChurnRetainSummary which is having relationships set with  Calendar, Product, ProductSales, Customer, Country.  Report already has filters placed on Month/Year, ProductCategory, ProductAccessLength(for subscription products), Country.  

Now I have this requirement to build a table on top of LifeTime to calculate SummarizeColumns on Measures with Group By on MMMYYYY, LifeTime month and want filters on ProductCategory,  ProductAccessLength and Country. 

 

LifetTimeSummary = SummarizeColumns(MMMYYYY, LifeTimeMonth, Calculate(Measure1),Calculate(Measure2) ...) I get filters on MMMYYYY to work properly but other filters on ProductCategory,  ProductAccessLength and Country don't work at all because no column related to that is added so added in Group by  

SummarizeColumns(MMMYYYY, LifeTimeMonth,ProductCategory,  ProductAccessLength,Country ,Calculate(Measure1),Calculate(Measure2)) but now naturally measures are getting calculated at that granularity and causing problem as they get sum up..

 

So what I really want is grouping on  MMMYYYY, LifeTimeMonth and filtering on ProductCategory,  ProductAccessLength,Country  to work.

How do I acheive this, I tried following

VAR filter1 = Filter(ProductCategory = "Instrument")

VAR filter2 = Filter(ProductAccessLength= "1month")

VAR filter3 = Filter(Country = "India")

 

SummarizeColumns(MMMYYYY, LifeTimeMonth,filter1,  filter2, filter3,Calculate(Measure1),Calculate(Measure2))

 

BUT in above I am hardcoding values for filter1, filter2 and filter3 instead they should come from slicer/filter used in report when I bind   LifetTimeSummary to matrix/pivot table with MMMYYYY on rows and LifeTimeMonth on columns but other columns as filters.

 

Any help would be appreciated.

 

2 REPLIES 2
v-deddai1-msft
Community Support
Community Support

Hi @Anonymous ,

 

Based on your description, you want to pass filters from slicer or other things to the calculated table? I don't think it will work, the calculated table will be calculated when the data model refreshes, and in other situations, it is fixed.

 

You can use the  LifetTimeSummary as parameter in your measures, But I suggest you use summarize or addcolumns instead of SummarizeColumns, the expressions that  in SummarizeColumns only have the filter context in itself, not the row context (SUMMARIZE provides the row context).

 

If you need accurately help, please show us sample pbix file.

 

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

MFelix
Super User
Super User

Hi @Anonymous ,

 

You cannot create a table using the result of a measure. Measure are calculated on context (filters, visualizations, slicers, columns, ...) and tables are on row context.

 

This means that if you create a table using a measure tha calculation is static and the filtering need to be place manually has you refer.

 

For this case you need to create a measure that can then be used for the visualization you need.

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.