Dax - IF statement comparing two measures

[KellyDittmar]

I'm sure there is an obvious solution to this issue - it's awfully hard to google search for help with an if statement! 

I work for a consumer goods company - we sell items to customers at MSRP. When we decide to clearance an item, we sell it at X% less than MSRP. My end users desire some reporting to determine if something has been sold at a "close out" level - defined as 65% of MSRP. 

My data source is an SSAS tabular cube. We have attributes of our sales - including the customer PO, order date & item(s).

* The sales table level of granulartiy is at a line item level.

* Sales joins to the Order Date Dimension & the Items Dimension.

* A sale has 1 order date with associated columns of Date, Month, Year (and is dense). Each order has 1 and only 1 order date.

* This is all working correctly.

I have created two measures - both of which are working great. 

[Shipped Sales $] - the sum of all shipped dollars

[Shipped MSRP] - pseudocode: sum(Item's MSRP * Units Ordered)

I then created a new DAX measure:

Consumer Sales Type = IF([Shipped Sales $]<([Shipped MSRP]*.65),"Closeout Sales","Regular Sales")

This is where it starts to go poorly... See the attached screenshot.

* I am looking for orders between 3/24/2018 and 4/3/2018. Off-screen - I have another filter to bring in just the one customer PO. 

* Top table is my dataset without my measure. 1 PO ordered on 1 date in one month/year for 1 PCR with a shipped and MSRP of $40.

* The bottom dataset is with my measure - which explodes my result set. Interestingly - Order Date/Month/Year all come from the same record - but it's like the context is lost between them. I still only have the 1 shipped unit for $40, but the duplication indicates an issue which I can't find.

What I've tried:

* Sum to wrap my measures - made no difference

* adding a second if: IF(not(ISBLANK([Shipped Sales $])) to wrap my original measure - but of course this just hides my bad data (and eventually I run out of memory so this is not a workable solution). 

Any ideas why this is happening? I can certainly create it as a calculated column on my sales table - but I believe there will be cases when they don't want to use 65% as the threshold and I hate to have to change my model constantly... 

[RobertSlattery]

Hi, what happens if you edit the relationship between the sales table and the date dimension and set your cross filter direction to "Both"?

Also, if it is a field in the sales table, it looks like there is something wrong with your [Order Month] field.

[KellyDittmar]

Order month is on my date dimension and works perfectly without my bad measure... Which us confusing.

[RobertSlattery]

Yes, DAX is always confusing when you're dealing with measures.

Did you try my suggestion with the relationships?

[KellyDittmar]

@RobertSlattery - I had to get back to my work computer to try it... Unfortunately, I don't have SS 2016 - so I cannot set that as an option.  

[KellyDittmar]

That being said... I think you're onto something... 

I just changed the measure I need to be this:

Consumer Sales Type = IF(45=45,"Closeout Sales","Regular Sales")

And it still brought in all of my "garbage rows". 

[RobertSlattery]

Based on your original post, it looks like your Consumer Sales Type is a calculated column rather than a measure. Can you confirm?  It would also help if you published the exact code rather than pseudo code for the measures as well.

[KellyDittmar]

Nope, it's a measure... created with a right click 'create measure' and defined as this:

And for my other two... sure - here are they definations. These are also measures. 

Shipped Sales $:=sum(Sales[Shipped Dollars Column])

Shipped MSRP:=sum(Sales[Shipped MSRP Column])

[Anonymous]

Ran into almost this exact scenario, and have been left scratching my head. Seems like somehow the simple use of an operator [measure1] < [measure2] is somehow forcing a row context, and resulting in a cross join between the tables included in the visual. 

In my case, I boiled it down to see what was happening, and eliminated the IF statement entirely. I would expect [measure1] < [measure2] to return a TRUE or FALSE (and it does), but it causes the crossjoin. 

The only workaround I found was like yours - filter out junk rows by looking for blanks just before returning result, but I'd really like to understand WHY this is happening. 

Were you able to find a better explanation? This thread looks un-resolved. 

[Anonymous]

Hello, I had the same problem as I wanted to create a measure by comparing two measures. Calculation was a never-ending process in the visualization, as I think if a scalar function is applied then PBI wants to do a cross join for all affected tables involved for the measure on the data model.

What I tried was:

Add a column on the dimensional table. Formula was IF(Measure1 = 0; Measure2; Measure1). It calculated all the values on row level, and it was working on the visuals.

[ChandeepChhabra]

@KellyDittmar Can I please take a look at your power bi file ?

[KellyDittmar]

I'm sorry... I can't share the cube because if the company centric dara contained there in, and without that, I don't think the report is helpful?