Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

SUMMARIZE FUNCTION in measures

Hello,

 

I am familiar using desktop and creatigng reports with data imported.

Now I jumped into live connectivity and I only can consult tables but not create new ones. I have to base my calculation in measures. 

So I would like to get more familiar with SUMMARIZE function. 

I can do this measure:

Measure =
COUNTX(SUMMARIZE(Event,(Event[EventCode]), "ppp", MAX(Event[EventCode])), [ppp])
 
But if I want to take the count of  a specific event code, then it is not displayed. What am I doing wrong? 
COUNTX(SUMMARIZE(Event,(Event[EventCode]), "ppp", (Event[EventCode]) = "1234"), [ppp])
 
 
On another hand. If I have a matrix like this (created with data improted) and I want to create a measure to count the number of values >1.00 how can I do it? 
 
"Values" is a measure 

 

I guess that I should first of all craete the matrix with summarize? Please help me. Thanks

Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

    You may want to spend time with this article.

    Nested grouping using GROUPBY vs SUMMARIZE - SQLBI

     

    Your second question can be addressed by combining SUMMARIZE, ADDCOLUMNS, and COUNTROWS (or COUNTX if you prefer).  It will require multiple steps in your measure (temporary table variables).  Watch for context transitions.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    I have built a data sample like this to help you understand:

     

    For Q1:

     

    So based on my test, you could use the following formula:

    EventCode is 1234 == COUNTX(FILTER('Event','Event'[EventCode]="1234"),[EventCode])

     

    For Q2:

    Please try:

    >1 = CALCULATE(COUNTROWS('Matrix'),FILTER('Matrix','Matrix'[Value]>1))

     

    The final output is shown below:

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

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    You may want to spend time with this article.

    Nested grouping using GROUPBY vs SUMMARIZE - SQLBI

     

    Your second question can be addressed by combining SUMMARIZE, ADDCOLUMNS, and COUNTROWS (or COUNTX if you prefer).  It will require multiple steps in your measure (temporary table variables).  Watch for context transitions.