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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
googlogmob
Advocate II
Advocate II

SUM of SUMMARIZECOLUMNS result

I built measure to return 1 if user have made at least one calls over a day. So I have to get count of days when user tried to call

 

 

Calls = CALCULATE(COUNT(Calls[CALL_ID]);
USERELATIONSHIP(Calls[USER_ID];Users[USER_ID]);
USERELATIONSHIP(Calls[CALL_DATE];Calendar[Date]))

 

 

Period = 
VAR res = 
SUMMARIZECOLUMNS(Users[USER_ID];
		 Calendar[Date];
		 "Period";SUMX(Users;IF([Calls]>0;1;BLANK())))
RETURN res

 When I create separete table using Period measure, I get true calculation:

http://prntscr.com/hoo52e

But when i try to calculate 

SumOfPeriods = 
VAR res = 
SUMMARIZECOLUMNS(Users[USER_ID];
		 Calendar[Date];
		 "Periods";SUMX(Users;IF([Calls]>0;1;BLANK())))

RETURN SUMX(res;[Periods])

I get error:

 

 

SummarizeColumns() and AddMissingItems() may not be used in this context

Is it possible to calculate sum of SUMMARIZECOLUMNS result?
Help please

1 ACCEPTED SOLUTION
googlogmob
Advocate II
Advocate II

Here is answer
It was necessary to add USERELATIONSHIP in CALCULATETABLE for data-table

Periods = 
SUMX(
 SUMMARIZE(
  CALCULATETABLE(Calls;
                USERELATIONSHIP(Calls[USER_ID];Users[USER_ID]);
				USERELATIONSHIP(Calls[CALL_DATE];Calendar[Date]));
 Calendar[Date];Users[USER_ID];"Periods";IF([Calls]>0;1;BLANK()));[Periods])

 

 

View solution in original post

1 REPLY 1
googlogmob
Advocate II
Advocate II

Here is answer
It was necessary to add USERELATIONSHIP in CALCULATETABLE for data-table

Periods = 
SUMX(
 SUMMARIZE(
  CALCULATETABLE(Calls;
                USERELATIONSHIP(Calls[USER_ID];Users[USER_ID]);
				USERELATIONSHIP(Calls[CALL_DATE];Calendar[Date]));
 Calendar[Date];Users[USER_ID];"Periods";IF([Calls]>0;1;BLANK()));[Periods])

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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