Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Greg_Deckler
Super User
Super User

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:

 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
2 ACCEPTED SOLUTIONS

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

View solution in original post

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

View solution in original post

4 REPLIES 4

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.