Forum Discussion

KellyDittmar's avatar
KellyDittmar
Advocate IV
8 years ago

Dax - IF statement comparing two measures

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... 

11 Replies

    • KellyDittmar's avatar
      KellyDittmar
      Advocate IV
      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?
  • RobertSlattery's avatar
    RobertSlattery
    Responsive Resident

    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's avatar
      KellyDittmar
      Advocate IV
      Order month is on my date dimension and works perfectly without my bad measure... Which us confusing.
      • RobertSlattery's avatar
        RobertSlattery
        Responsive Resident

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

         

        Did you try my suggestion with the relationships?