Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Ran across an odd issue at a customer the other day and thought that perhaps @marcorusso or someone else familiar with the bowels of DAX could help explain it. I published the PBIX here and it is also attached:
Basically, the data model is very simple:
Customers 1--<>--* Assessments 1--<>--1 Fact
I have 2 measures:
SumScore = SUM('Fact'[Score]) InvScore = 1 - [SumScore]
When I create a bar chart using Date from Assessment and InvScore, something strange happens. If I click a particular customer in a slicer, I get a score for each Date, even though a customer only has a single assessment. What I would have expected is that selecting a customer filters Assessment which then filters the [Score] and only return a value for the single Date that customer has in the Assessments table. Instead, the InvScore measure somehow "sees" the other dates in the Assessment table and calculates a value. Seems odd to me so I figured I'd pose the question out to the group.
Solved! Go to Solution.
The reason is simple:
1 - BLANK() = 1
More info in Handling BLANK in DAX article.
You probably want to do this:
InvScore = VAR __score = [SumScore] VAR __value = (1 - __score) * (NOT ISBLANK(__score)) RETURN __value
Marco Russo - SQLBI
Because a report is a crossjoin between columns you include unless auto-exists is triggered.
Which I think it's not the case in your scenario.
BTW: a one-to-one relationship is usually a very bad idea - I'd use iit only if there are no other choices importing data from two different data sources.
Marco Russo - SQLBI
The reason is simple:
1 - BLANK() = 1
More info in Handling BLANK in DAX article.
You probably want to do this:
InvScore = VAR __score = [SumScore] VAR __value = (1 - __score) * (NOT ISBLANK(__score)) RETURN __value
Marco Russo - SQLBI
Thanks @marcorusso - I understand why the measure returns 1 for those years, the 1-BLANK() and your fix is exactly the work-around that I used. My concern was that I didn't think that the measure should "see" those other dates in the table and thus calculate anything. My thinking was, Customer filters down Assessments to a single Date and the measure therefore should only be calculated for that Date. But apparently that's not what is occurring and I was curious as to why.
Because a report is a crossjoin between columns you include unless auto-exists is triggered.
Which I think it's not the case in your scenario.
BTW: a one-to-one relationship is usually a very bad idea - I'd use iit only if there are no other choices importing data from two different data sources.
Marco Russo - SQLBI
@marcorusso - I agree about the 1:1, it just happened to come out that way because I was plugging in test data and didn't feel like entering more data than I needed to! In the real customer scenario it is a 1:*. Thanks for the link to auto-exists!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |