topic How to use measure inside Summarize Function in DAX Commands and Tips

How to use measure inside Summarize Function

Amar-Agnihotri — Thu, 21 Oct 2021 09:06:37 GMT

I want to use a measure inside this summarize function -

Table F1 = SUMMARIZE (
SELECTCOLUMNS (
'Opportunity Snapshot',
"Source", 'Opportunity Snapshot'[Start of Quarter - Copy],
"Target", 'Opportunity Snapshot'[Flow one Result],
"Opportunity ID",'Opportunity Snapshot'[Opportunity_ID_18__c],
"Quarter",'Opportunity Snapshot'[Quarter]
),
[Source],
[Target],
[Opportunity ID],
[Quarter],
"Total", SUM ( 'Opportunity Snapshot'[Incremental ACV (USD)] )

)

Rather than using SUM function i want to use this measure -

Measure -
Last ACV =
VAR _id = MAX ('Opportunity Snapshot'[Opportunity_ID_18__c])
VAR _date = CALCULATE( MAX('Opportunity Snapshot'[Close_Date__c]), ALLSELECTED ('Opportunity Snapshot'), 'Opportunity Snapshot'[Opportunity_ID_18__c] = _id)
RETURN
CALCULATE (SELECTEDVALUE( 'Opportunity Snapshot'[Incremental ACV (USD)] ), VALUES ('Opportunity Snapshot'[Opportunity_ID_18__c]),'Opportunity Snapshot'[Opportunity_ID_18__c] = _id,'Opportunity Snapshot'[Close_Date__c] = _date )

I am unable to use measure because what i believe SUMMARIZE function doesn't allow you to do that. Can anybody help me out regarding that how can i achieve this

Re: How to use measure inside Summarize Function

v-robertq-msft — Mon, 25 Oct 2021 08:19:55 GMT

Hi,

As far as I’m concerned, you can use the measures you created before in the newly created Summarize function in the <expression> parameter after the parameter <groupBy_columnName> and <name>, you can use the measure in your DAX formula like this:

Table F1 = SUMMARIZE ( SELECTCOLUMNS ( 'Opportunity Snapshot', "Source", 'Opportunity Snapshot'[Start of Quarter - Copy], "Target", 'Opportunity Snapshot'[Flow one Result], "Opportunity ID",'Opportunity Snapshot'[Opportunity_ID_18__c], "Quarter",'Opportunity Snapshot'[Quarter] ), [Source], [Target], [Opportunity ID], [Quarter], "Total", [Last ACV] ) )

You can change the measure formula based on your needs.

For more info, please refer to this link:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/How-to-use-measure-inside-Summarize-Function/td-p/2147727

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to use measure inside Summarize Function

Amar-Agnihotri — Wed, 27 Oct 2021 11:45:30 GMT

Hi v-robertq-msft Thanks for the reply. Actually i was doing a small mistake.. issue has been resolved now