Odd Issue with Dates

[Greg_Deckler]

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:

https://app.powerbi.com/view?r=eyJrIjoiNDlkODNlMDMtZWZmNy00N2UyLTgwN2EtOGU1N2ZiZmNiNmQxIiwidCI6IjRhM...

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.

[marcorusso · Accepted 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

[marcorusso · Accepted 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

[Greg_Deckler]

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.

[marcorusso · Accepted solution]

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

[Greg_Deckler]

@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!