Calculate a measure in a group without using SUMMARIZE

[mbahonen]

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) . 

[mbahonen · Accepted solution]

Update. I believe I found a solution to my issue. Reference this post: Solved: Re: Subtotaling with SUMX and Summarize - Microsoft Fabric Community 

[mbahonen]

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.

Quartile % Bucket =

    IF( [Quartile %] >= .76, 1, 0)

I can get the measure to calculate, but when I put it into the Matrix, it destroys all the filters (so the small population above which is filtered by manager, shows the whole population and doesn't seem to aggregate (unless I use another matrix to filter one person only). This is the problem the SUMX + SUMMARIZE seems to solve, but I can't find another way to get there.

Any thoughts on how to modify the Quartile % Bucket measure so that it replicates the 2nd matrix above but maintains all filter contexts? 

[marcelsmaglhaes]

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

[mbahonen]

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?

[mbahonen]

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:

CALCULATE(

    [Measure with % Calculation] ,

    'Table1'[Start of Week Date] >= [VAR PreviousDateConverted] &&

    'Table1'[Start of Week Date] <= [VAR CurrentDate],

    ALLEXCEPT('Table1', 'Table1'[ID])

    )

The error I get is the following: 'A function "PLACEHOLDER' has been used in a True/False expresssion that is used as a table filter expression. This is not allowed.'

[Greg_Deckler]

@mbahonen Can you post sample data? Also, can you post the code to your Measure With % Calculation?

[mbahonen]

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).