Forum Discussion

91asma2's avatar
91asma2
Helper I
5 years ago

Summarize Function

OwenAuger's avatar
OwenAuger
Super User
5 years ago

Hi 91asma2 

 

Below are some ideas for improving performance. Would also be useful to see data model diagram or get a copy of pbix with sanitised data.

 

The principles I have applied are:

  1. In User with > 1 Disorder, Best not to add extension columns within SUMMARIZE. In this case, SUMMARIZE ( Assessment, Assessment[User ID] ) is equivalent to VALUES ( Assessment[User ID] ), so simplified to that.
  2. In Disorders Ct, it's more efficient to filter columns, not the entire Assessment table. To retain the existing filter context (which FILTER ( Assessment,...) would have done), wrapped the filter arguments of CALCULATE in KEEPFILTERS.

 

 

User with > 1 Disorder = 
COUNTROWS (
    FILTER (
        VALUES ( Assessment[User ID] ),
        [Disorders Ct] > 1
    )
)

 

 

 

 

 

Disorders Ct =
CALCULATE (
    DISTINCTCOUNT ( Assessment[assessment_id] ),
    KEEPFILTERS ( Assessment[Assess Score] = 1 ),
    KEEPFILTERS ( Assessment[Assess Category]
        IN { "ADDICTION", "ADHD", "APNEA", " DEPRESSION", "GEN_ANX", "PTSD", "SOC_ANX" } )
)

 

One other possible tweak which I didn't apply is to use SUMX/SUMMARIZE in place of DISTINCTCOUNT, which can help in certain data models. See here

 

I would be interested to know if this improves performance at all, otherwise might have to take a further look.

 

Regards,

Owen

 

  • 91asma2's avatar
    91asma2
    Helper I
    5 years ago

    Thank you so much. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      91asma2 

       

      Of course you're getting BLANKS. That's obvious when you look at your measure and think for a sec. You're trying to filter AssesmentID's using the [Disorders Ct] measure. It's obvious that for any one particular assessment the measure will return either 0 or 1. So the filter removes all assessments.

       

      You should not have changed the measure OwenAuger gave you because if you want to count the USERS where [Disorders Ct] > 1, then it makes no sense whatsoever to filter assessments by the mentioned measure.

       

      By the way, one more way to write the measure is this:

       

      Disorders Ct =
var Score_ = 
    SELECTCOLUMNS(
        {1},
        "@Score", [Value]
    )
var Category_ = {
        "ADDICTION",
        "ADHD",
        "APNEA",
        " DEPRESSION",
        "GEN_ANX",
        "PTSD",
        "SOC_ANX"
    }
var Filter_ =
    CROSSJOIN(
        Score_,
        Category_
    )
var Result =
    CALCULATE(
        DISTINCTCOUNT( Assessment[assessment_id] ),
        // If you want to obey any filters that are
        // already present on either [Assses Score]
        // or [Assess Category] you have to wrap
        // the TREATAS in KEEPFILTERS. If in doubt,
        // just use this measure first and then the
        // version with:
        //
        //KEEPFILTERS(
        //    TREATAS(
        //        Filter_,
        //        Assessment[Assess Score],
        //        Assessment[Assess Category]
        //    )
        //)
        //
        // KEEPFILTERS enables you to join filters
        // in the measure and coming from outside
        // in an AND operation instead of overwriting
        // which is the usual semantics of filters in
        // CALCULATE.
        TREATAS(
            Filter_,
            Assessment[Assess Score],
            Assessment[Assess Category]
        )
    )
return
    Result