Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NilR
Post Patron
Post Patron

How reference SUMMARIZE measure in another measure?

If I create a Summarize as below how can I refrence in another measure? I also tried with Group By and still get the same error. It works as variable, but not stand alone measure.

 

 

 

 

 

Grps = 
var _Max_Date = MAX('Calendar Service'[Date])
var _Min_Date = MIN('Calendar Service'[Date])
VAR _PHASE = ALLSELECTED('DIM'[PHASE])


 RETURN 
  SUMMARIZE(FILTER(Group_Details,([DATE] >= _Min_Date && [DATE]<= _Max_Date),'001 Table'[Grp_NB])

 

 

 

 

 

 

Use it in below measure:

 

 

 

 

 

Measure 2-A= 
CALCULATE(SUMX(VALUES('Table'[ID]),1), KEEPFILTERS(NOT 'Table'[GrpNum] IN [Grps] ))

or 

Measure 2-B=
SUMMARIZE(
        FILTER( 'Table' , NOT [Grps] IN [Grps] &&  ALLSELECTED('Table'[_ID]) IN [ID_12] 
        && ('Table'[Date] >= _Max_12_MOS &&  'Table'[Date] <= _Max_Date)),'Table'[GrpNum])

 

 

 

 

 

I get an error: [Grps] is not a valid Table.

4 REPLIES 4
v-rongtiep-msft
Community Support
Community Support

Hi @NilR ,

Does that make sense? If so, kindly mark @amitchandak 's answer as the solution to close the case please. Thanks in advance.

 

Best Regards

Community Support Team _ Polly

 

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

unfortuntly, this is what I had and trying to create something similar to dynamic table with either group by or Summarize 

amitchandak
Super User
Super User

@NilR , keep summarize code in measure else it will be static

 

Grps =
var _Max_Date = MAX('Calendar Service'[Date])
var _Min_Date = MIN('Calendar Service'[Date])
VAR _PHASE = ALLSELECTED('DIM'[PHASE])
var _tab = SUMMARIZE(FILTER(Group_Details,([DATE] >= _Min_Date && [DATE]<= _Max_Date),'001 Table'[Grp_NB])
RETURN
CALCULATE(SUMX(VALUES('Table'[ID]),1), filter('Table', NOT 'Table'[GrpNum] IN [Grps] ))

or

Grps =
var _Max_Date = MAX('Calendar Service'[Date])
var _Min_Date = MIN('Calendar Service'[Date])
VAR _PHASE = ALLSELECTED('DIM'[PHASE])
var _tab = SUMMARIZE(FILTER(Group_Details,([DATE] >= _Min_Date && [DATE]<= _Max_Date),'001 Table'[Grp_NB])
RETURN
CALCULATE(SUMX(VALUES('Table'[ID]),1), filter(all('Table'[GrpNum]), NOT 'Table'[GrpNum] IN [Grps] ))

@amitchandak Thank you! I have about 6 measures using the Summaries and I was hoping to pull this out to increase the performance. It takes decades to load 🤦‍

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors