Summarize Function

[Anonymous]

@91asma2, I did tell you exactly why you get blanks. It's now up to you, I guess, what you want to do with this knowledge. If you still don't understand why you're getting blanks, then you have no choice but to revise/learn how context transition works in conjunction with CALCULATE in iterators like SUMX or FILTER. I guess there's no way around this, unfortunately.

You can start with this: How CALCULATE works in DAX - SQLBI

And please do not fall into the trap as many others do when they think that you can write correct DAX without learning the theory behind it. DAX can't be learned from examples. You have to learn the theory. Sorry.

[OwenAuger]

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]

Thank you so much. 

[Anonymous]

@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