Forum Discussion
Odd Issue with Dates
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!
