Forum Discussion
Odd Issue with Dates
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.
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
4 Replies
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
