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.
@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.
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:
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
Thank you so much.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |