cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cymbolz Regular Visitor
Regular Visitor

IF statement unexpected result evaluation

I have "solved" this with a better expression, but am dumbfounded as to why.

 

This is from a colleague, I'm unable to share the pbix without effort so hoping this is sufficient to pin-point the cause.

 

  • Single table data model
  • Report has matrix by several dimension columns and values of several measure
  • Handful of measures, all fairly simple using CALCULATE+ALL or SUM or DIVIDE
  • No iterators
  • Measures re-use other measures

The problem is with this evaluation:

 

[Problem Measure] = IF ( [Measure X] >= 0, [Measure X], 0 )

The logical test appears to be working fine.  But the evaluation of [Measure X] in the result-if-true expression is producing a different result.

 

Eg - the last two rows should simply show as the same value as the first column for [Problem Measure]

 

Measure X     Problem Measure
-10              0
-5               0
-1               0
 0               0
 5             -20
10             -11

The fix was to use a variable and not repeat the measure evaluation.  Which is also better practice.  

 

But I am puzzled why the original expression is not producing the expacted result for [Measure X].  I've broken it down by expanding each measure, run lots of tests and variations in DAX studio to pin-point the issue.  I'm back to bare bones DAX functions and yet it still comes to the evaluation of the true expression behaving differently.  

 

Feels so obvious but I can't think of it...help please?!?!

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: IF statement unexpected result evaluation

@Cymbolz  The fix is scheduled to release in June version. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Community Support Team
Community Support Team

Re: IF statement unexpected result evaluation

Hi @Cymbolz ,

 

If they are in the Value field and in the same visual, the result should be correct. 

1. Are they measures? 

2. Where are they?

Can you share a DUMMY sample? It seems easy to create a dummy one. 

You can upload it to the cloud drive like OneDrive then share the download link here. Please mask the sensitive parts.

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cymbolz Regular Visitor
Regular Visitor

Re: IF statement unexpected result evaluation

Here's a clean PBIX: https://excelconsultingsolutions-my.sharepoint.com/:u:/g/personal/matt_excelconsulting_com_au/ETD-66...

 

In the matrix you'll see the measure [Opp] which is the culprit:

 

 

Opp = IF([Opportunity]>=0,[Opportunity],0)

 

 

 

I have used variables to create [Opp MATT] which works as expected.  I'm not familiar with all the business logic and this is not my creation.  Am only interested in being able to explain why the conditional test then produces what looks like a different result.

 

 

Cymbolz Regular Visitor
Regular Visitor

Re: IF statement unexpected result evaluation

@v-jiascu-msft Any ideas?  It has stumped me...

Community Support Team
Community Support Team

Re: IF statement unexpected result evaluation

Hi @Cymbolz ,

 

I have reported this issue to the Product Team. I will update later. 

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: IF statement unexpected result evaluation

The report ID is CRI 111436797.

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cymbolz Regular Visitor
Regular Visitor

Re: IF statement unexpected result evaluation

Thank you Dale.  Is there a way I can see / track the progress based on this CRI ?

Cymbolz Regular Visitor
Regular Visitor

Re: IF statement unexpected result evaluation

@v-jiascu-msft  What is the status please? And any way I can check up on it myself?


Thanks,
Matt

Moderator v-qiuyu-msft
Moderator

Re: IF statement unexpected result evaluation

@Cymbolz  The fix is scheduled to release in June version. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.