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
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.
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
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCheck out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
97 | |
93 | |
87 | |
68 |
User | Count |
---|---|
173 | |
134 | |
132 | |
102 | |
95 |