Forum Discussion

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago
Solved

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...
Need Help
  • marcorusso's avatar
    marcorusso
    7 years ago

    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's avatar
    marcorusso
    7 years ago

    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's avatar
marcorusso
Most Valuable Professional
7 years ago

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's avatar
Greg_Deckler
Community Champion
7 years ago

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!