VARs inside SUMMARIZE dont work?

[Metricbits]

Hello,

I have problems using VARs inside SUMMARIZE. If I assign expression within the measure - it doesn't work. However, if I create a separate measure, it works fine.

Here is an example that works:

Measure 1:

NDR_AveRateBased_Charged =
AVERAGEX ( PipelinesMerged, PipelinesMerged[Client Charge Rate] ) * [NDR_Units_Paid]

Measure 2:

TEST1 =
VAR Step1 =
    IF ( HASONEFILTER ( Astute_Export[EmployeeID] ), [NDR_AveRateBased_Charged] )
VAR Step2 =
    SUMMARIZE (
        Astute_Export,
        Astute_Export[EmployeeID],
        "XYZ", [NDR_AveRateBased_Charged]
    )
RETURN
    SUMX ( Step2, [XYZ] )

However, I would like to avoid creating 2 measures for this, and when I try to put 1st measure as VAR, it returns much higher numbers. Here is an example:

TEST1 =
VAR Step0 =
    AVERAGEX ( PipelinesMerged, PipelinesMerged[Client Charge Rate] ) * [NDR_Units_Paid]
VAR Step1 =
    IF ( HASONEFILTER ( Astute_Export[EmployeeID] ), Step0 )
VAR Step2 =
    SUMMARIZE ( Astute_Export, Astute_Export[EmployeeID], "XYZ", Step0 )
RETURN
    SUMX ( Step2, [XYZ] )

I haven't used SUMMARIZE much before, the similar approach works with other functions, but SUMMARIZE I have problem with. Please help.

[Fowmy · Accepted solution]

@Metricbits 

When a variable is calculated and stored, it is static, in your case, you need to evaluate Step0 formula in a new filter context so the formula has to be:

TEST1 =

VAR Step2 =
    SUMMARIZE ( Astute_Export, Astute_Export[EmployeeID], 
        "XYZ", 
        AVERAGEX ( PipelinesMerged, PipelinesMerged[Client Charge Rate] ) * [NDR_Units_Paid]
    )
RETURN
   if( HASONEFILTER ( Astute_Export[EmployeeID] ),
       SUMX ( Step2, [XYZ] ),
        BLANK()
    )

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn