Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I've been trying to find a better way to fix this calculation for a while and nothing I have tried works. I hope I can explain.
Here is my current meaure:
MeasureName = SUMX( SUMMARIZE('Table1' , 'Table1'[ID] ) , [Measure with % Calculation] )
There are many other measures that feed into this one that would be too complicated to explain. Basically I believe after researching, that the SUMMARIZE function causes performance issues, and its causing my matrix to be very slow, so I'd like to rewrite it a different way, but I can't seem to replicate another way to use the measure (instead of calculating a SUM of a column total) AND group on the Employee ID.
I've tried rewriting it like this (also I need to use dynamic time intelligence so some of these measures are written in ways to accommodate that and include the dates like below):
MeasureName = CALCULATE( [Measure with % Calculation] , FILTER( 'Table1' , 'Table1'[Start of Week Date] >= [VAR PreviousDateConverted] && 'Table1'[Start of Week Date] <= [VAR CurrentDate] ) )
However, the above won't group by the ID like I need it to.
I've also tried variations using ADDCOLUMNS and SUMMARIZECOLUMNS but can't seem to get anything to work. Any suggestions at all are appreciated.
What i'm trying to do:
Basically this calculation is taking a weekly total of 1's and 0's for each ID and aggregating it to a % of the total of weeks within a dynamic slicer variable (so the user selects the # of weeks to determine the % of total). It then throws that % of total into buckets (that are each separate measures) .
Solved! Go to Solution.
Update. I believe I found a solution to my issue. Reference this post: Solved: Re: Subtotaling with SUMX and Summarize - Microsoft Fabric Community
Update. I believe I found a solution to my issue. Reference this post: Solved: Re: Subtotaling with SUMX and Summarize - Microsoft Fabric Community
Ok, so new take on my question. Ultimately what I'm trying to do is the following.
I have a results table below made entirely of measures. I am able to get the below calculations to work fine. Quartile is Weeks Compliant / Total Weeks.
Name | Weeks Compliant | Total Weeks | Quartile % |
Bob Smith | 6 | 6 | 100% |
Susan Fox | 5 | 6 | 83% |
John Anderson | 4 | 6 | 67% |
Dave Johnson | 2 | 6 | 33% |
Jane White | 1 | 6 | 17% |
Jeff George | 0 | 6 | 0% |
The Quartile % determines a Grouping (or bucket) that each person is placed into in the final following Matrix.
Name | Group 1 (0 to 25%) | Group 2 (26 to 50%) | Group 3 (51 to 75%) | Group 4 (76 to 100%) |
Bob Smith | 1 | |||
Susan Fox | 1 | |||
John Anderson | 1 | |||
Dave Johnson | 1 | |||
Jane White | 1 | |||
Jeff George | 1 |
I tried a calculation to create a bucket group that would bypass my original SUMX + SUMMARIZE calcultions that I'm trying to avoid. This is just a test version of what I created using only the 1st Group.
Hey @mbahonen ,
Maybe you can try something like that:
ImprovedMeasureName =
CALCULATE(
[Measure with % Calculation],
ALLEXCEPT('Table1', 'Table1'[ID])
)
The `SUMMARIZE` function is conceptually similar to summarizing the data. It creates a table with summarized values based on the columns you specify, and then you can perform calculations on that summarized table.
The key difference is that `SUMMARIZE` is often used to create intermediary summary tables that can be referenced in subsequent calculations, while the `ALLEXCEPT` function is used to remove filters from all columns except those specified, effectively reducing the context in which calculations are performed.
In your case, `ALLEXCEPT` is used to maintain only the 'ID' column's filter context, which can improve performance by reducing the number of rows considered in the calculation.
However, the exact choice between `SUMMARIZE` and `ALLEXCEPT` depends on your specific data model and performance requirements. Each has its use cases, and performance implications can vary.
It's important to note that optimizing DAX measures sometimes requires tuning the data model itself, and in cases where the model is large and complex, it can lead to slower query performance. Hence, the effectiveness of any DAX measure may vary based on individual circumstances and model design. It's essential to consider these factors and possibly engage in data model optimization for the best results.
If this post helps, please mark as solved.
Cheers,
Marcel
Regards,
Marcel Magalhães
Microsoft Power BI Official Partner
MCT | Certified PL-300 Power BI
Ok update:
This kinda works:
ImprovedMeasureName =
CALCULATE(
[Measure with % Calculation],
ALLEXCEPT('Table1', 'Table1'[ID])
)
However, what is strange is that when I put the metric into a Matrix, when I have the full list of Employees, the results are incorrect (it gives a true result or a 1 for all records), but if I use another table to filter for an individual, it gives the correct value for that individual. I'm not remotely sure what is happening here. I'm sure it will be difficult to assist as I haven't given the full predacessor metrics and columns that build my main measure, but any thoughts on what could be going on?
Hi there,
I've actually tried that version already, but unfortunately it doesn't work as hoped. It doesn't account for the dynamic time intelligence slicer portion (see "rewritten" version in original post), so its not calculating correctly. I think the SUMX + SUMMARIZE version takes into account the dynamic filter embedded in the [Measure with % Calculation] but when I don't use SUMX + SUMMARIZE, I can't seem to get the combination of the ID + # of weeks portion (aka the between start and end date).
Here's how I tried to rewrite your version but with some time intelligence added, but I get an error:
@mbahonen Can you post sample data? Also, can you post the code to your Measure With % Calculation?
Hi there,
I did post some sample data in a post above, but no worries. I've just found the solution (see other post marked as solution).