Forum Discussion

ka047's avatar
ka047
Helper I
5 years ago
Solved

DAX Measure help - First Post

I am trying to put an IF( AND( statements added into my dax measure (Which I will post below). The problem I am running into is where to put those statements & how to based on the field not being a measure. What the field is I am trying to input is such;

 

IF([DropShippedInd=0, [Shipdays] >=3 then it's On-Time. So if Delivery Date & Confirmed Ship Date are >=3 , 1,0)

 

Next is

 

IF([DropShippedInd=1, [Shipdays] >=1 then it's On-Time. So if Delivery Date & Confirmed Ship Date are >=1 , 1,0)

 

So the above needs to go somewhere in the DAX measure I have created below; with the [DropShippedInd] is a field, not a calculated measure. So may need to try like SUMX or something before to get it in.

 

OTD =
var summary =
summarize (Fact_SalesCogs,
Fact_SalesCogs[SalesOrder],
Fact_SalesCogs[ShippingDateConfirmed],
Fact_SalesCogs[DeliveryDate],
"Shipdays",
calculate(
AVERAGEX(
Fact_SalesCogs, datediff(Fact_SalesCogs[ShippingDateConfirmed], Fact_SalesCogs[DeliveryDate], day)
))

)

return
sumx(summary,
if(
and(
not(isblank([ShippingDateConfirmed])),
and(
not(isblank([DeliveryDate]))

,
[shipdays] >= 0
)
),
1,0
)

)
Need Help
  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi ka047 ,

    If you think there is something wrong with your yellow part lines in your fomula, you can modify it like this:

    NOT ISBLANK ( [DeliveryDate] ) && [Shipdays] == 0

    The whole formula:

    Measure 3 =
VAR summary =
    SUMMARIZE (
        Fact_SalesCogs,
        Fact_SalesCogs[SalesOrder],
        Fact_SalesCogs[ShippingDateConfirmed],
        Fact_SalesCogs[DeliveryDate],
        Fact_SalesCogs[DropShippedInd],
        "Shipdays",
            CALCULATE (
                AVERAGEX (
                    Fact_SalesCogs,
                    DATEDIFF (
                        Fact_SalesCogs[ShippingDateConfirmed],
                        Fact_SalesCogs[DeliveryDate],
                        DAY
                    )
                )
            )
    )
RETURN
    SUMX (
        summary,
        IF (
            NOT ISBLANK ( [ShippingDateConfirmed] )
                && (
                    NOT ISBLANK ( [DeliveryDate] )
                        && [Shipdays]
                            == 0
                                || (
                                    NOT ISBLANK ( [DeliveryDate] )
                                        && ( Fact_SalesCogs[DropShippedInd] = 1
                                        && [Shipdays] <= 1
                                        && [Shipdays] >= 0
                                        || Fact_SalesCogs[DropShippedInd] = 0
                                        && [Shipdays] <= 3
                                        && [Shipdays] >= 0 )
                                )
                ),
            1,
            0
        )
    )

    If [Shipdays] is a number type column, [Shipdays] = 0 and [Shipdays] == 0 are different:

     

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

11 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    instead of AND() you can use &&.

     

    if(a>3 && b=0 && c<10 && d=5,this,that)

     

    for OR() you can use ||

    • ka047's avatar
      ka047
      Helper I
      5 years ago

      For the below measure, what would change from what you referred to?

      Measure 3 = var summary =
      summarize (Fact_SalesCogs,
      Fact_SalesCogs[SalesOrder],
      Fact_SalesCogs[ShippingDateConfirmed],
      Fact_SalesCogs[DeliveryDate],
      Fact_SalesCogs[DropShippedInd],
      "Shipdays",
      calculate(
      AVERAGEX(
      Fact_SalesCogs, datediff( Fact_SalesCogs[ShippingDateConfirmed],Fact_SalesCogs[DeliveryDate], day)
      ))

      )

      return
      sumx(summary,
      if(
      and(
      not(isblank([ShippingDateConfirmed])),

      OR(
      and(
      not(isblank([DeliveryDate]))
      ,
      [Shipdays] = 0)
      ,
      and(
      not(ISBLANK([DeliveryDate]))
      ,
      or( Fact_SalesCogs[DropShippedInd] = 1 && AND(
      [Shipdays] <=1,[Shipdays] >=0),
      Fact_SalesCogs[DropShippedInd] = 0 && AND(
      [Shipdays] <=3,[Shipdays] >=0)
      )

      )
       
      )
      ),
      1,0
      )

      )
  • ka047's avatar
    ka047
    Helper I
    5 years ago

    I have now altered the DAX measure to be the below but unsure if it's working or not. Any help or confirmation??

     

    OTD=

    var summary =

    summarize (Fact_SalesCogs,

    Fact_SalesCogs[SalesOrder],

    Fact_SalesCogs[ShippingDateConfirmed],

    Fact_SalesCogs[DeliveryDate],

    Fact_SalesCogs[DropShippedInd],

    "Shipdays",

    calculate(

    AVERAGEX(

    Fact_SalesCogs, datediff( Fact_SalesCogs[ShippingDateConfirmed],Fact_SalesCogs[DeliveryDate], day)

    ))

     

    )

     

    return

    sumx(summary,

    if(

    and(

    not(isblank([ShippingDateConfirmed])),

     

    OR(

        and(

            not(isblank([DeliveryDate]))

    ,

    [Shipdays] = 0)

    ,

    and(

        not(ISBLANK([DeliveryDate]))

        ,

          or(  Fact_SalesCogs[DropShippedInd] = 1 && AND(

                [Shipdays] <=1,[Shipdays] >=0),

                Fact_SalesCogs[DropShippedInd] = 0 && AND(

                    [Shipdays] <=3,[Shipdays] >=0)

          )

     

        )

     

    )

    ),

    1,0

    )

     

    )

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    This is your formula:

    You can rewrite as:

    Full set:

    measure 3 :=
    VAR summary =
        SUMMARIZE (
            Fact_SalesCogs,
            Fact_SalesCogs[SalesOrder],
            Fact_SalesCogs[ShippingDateConfirmed],
            Fact_SalesCogs[DeliveryDate],
            Fact_SalesCogs[DropShippedInd],
            "Shipdays",
                CALCULATE (
                    AVERAGEX (
                        Fact_SalesCogs,
                        DATEDIFF (
                            Fact_SalesCogs[ShippingDateConfirmed],
                            Fact_SalesCogs[DeliveryDate],
                            DAY
                        )
                    )
                )
        )
    RETURN
        SUMX (
            summary,
            IF (
                NOT ISBLANK ( [ShippingDateConfirmed] )
                    && (
                        NOT ISBLANK ( [DeliveryDate] )
                            && [Shipdays] = 0
                            || (
                                NOT ISBLANK ( [DeliveryDate] )
                                    && ( Fact_SalesCogs[DropShippedInd] = 1
                                    && [Shipdays] <= 1
                                    && [Shipdays] >= 0
                                    || Fact_SalesCogs[DropShippedInd] = 0
                                    && [Shipdays] <= 3
                                    && [Shipdays] >= 0 )
                            )
                    ),
                1,
                0
            )
        )

     

    As you can see there are a couple of redundancies that you could eliminate with IN {}  statements.

    • ka047's avatar
      ka047
      Helper I
      5 years ago

      amitchandak lbendlin 

      I am wondering if we need the Shipdays = 0, at the first point. Because what I would expect is that by not making either DropShipppedInd  to be 0, and giving a threshhold of ❤️ or <1, that our ON-time deliveries would increase compared to before, when they had to be 0.

       

      I think the issue lies in this part;

      What I really want to return is if Delivery Date or confirmed shipping date aren't BLANK, then datediff(DeliveryDate, ShippingDateConfirmed,DAY) and the dropShippedInd's being either ❤️ or <1.

       

      Hope that makes sense.

      • v-yingjl's avatar
        v-yingjl
        Community Support
        5 years ago

        Hi ka047 ,

        If you think there is something wrong with your yellow part lines in your fomula, you can modify it like this:

        NOT ISBLANK ( [DeliveryDate] ) && [Shipdays] == 0

        The whole formula:

        Measure 3 =
VAR summary =
    SUMMARIZE (
        Fact_SalesCogs,
        Fact_SalesCogs[SalesOrder],
        Fact_SalesCogs[ShippingDateConfirmed],
        Fact_SalesCogs[DeliveryDate],
        Fact_SalesCogs[DropShippedInd],
        "Shipdays",
            CALCULATE (
                AVERAGEX (
                    Fact_SalesCogs,
                    DATEDIFF (
                        Fact_SalesCogs[ShippingDateConfirmed],
                        Fact_SalesCogs[DeliveryDate],
                        DAY
                    )
                )
            )
    )
RETURN
    SUMX (
        summary,
        IF (
            NOT ISBLANK ( [ShippingDateConfirmed] )
                && (
                    NOT ISBLANK ( [DeliveryDate] )
                        && [Shipdays]
                            == 0
                                || (
                                    NOT ISBLANK ( [DeliveryDate] )
                                        && ( Fact_SalesCogs[DropShippedInd] = 1
                                        && [Shipdays] <= 1
                                        && [Shipdays] >= 0
                                        || Fact_SalesCogs[DropShippedInd] = 0
                                        && [Shipdays] <= 3
                                        && [Shipdays] >= 0 )
                                )
                ),
            1,
            0
        )
    )

        If [Shipdays] is a number type column, [Shipdays] = 0 and [Shipdays] == 0 are different:

         

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

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    ka047 , You can create two new columns like

     

    Shipdays = datediff([Delivery Date],[Confirmed Ship Date],day)

     

    On time = if([Shipdays] >=3 && [DropShippedInd] =0 ,1 , 0)

     

    But I feel it should be [Shipdays] <=3

    • ka047's avatar
      ka047
      Helper I
      5 years ago

      So how would you write the measure based on what I have? I don't think I can create the columbs because we may have multiple fields that are in different tables but share the same name. Like Delivery Date

       

      Measure 3 = VAR summary =
      SUMMARIZE (
      Fact_SalesCogs,
      Fact_SalesCogs[SalesOrder],
      Fact_SalesCogs[ShippingDateConfirmed],
      Fact_SalesCogs[DeliveryDate],
      Fact_SalesCogs[DropShippedInd],
      "Shipdays",
      CALCULATE (
      AVERAGEX (
      Fact_SalesCogs,
      DATEDIFF (
      Fact_SalesCogs[ShippingDateConfirmed],
      Fact_SalesCogs[DeliveryDate],
      DAY
      )
      )
      )
      )
      RETURN
      SUMX (
      summary,
      IF (
      NOT ISBLANK ( [ShippingDateConfirmed] )
      && (
      NOT ISBLANK ( [DeliveryDate] )
      && [Shipdays] = 0
      || (
      NOT ISBLANK ( [DeliveryDate] )
      && ( Fact_SalesCogs[DropShippedInd] = 1
      && [Shipdays] <= 1
      && [Shipdays] >= 0
      || Fact_SalesCogs[DropShippedInd] = 0
      && [Shipdays] <= 3
      && [Shipdays] >= 0 )
      )
      ),
      1,
      0
      )
      )
  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Not fully, you have contradictions in your description (and cute little hearts 🙂 ).

     

    Write down your business logic, have someone else confirm it, then apply Boolean reduction.

    • ka047's avatar
      ka047
      Helper I
      5 years ago

      That heart should be < 3, I apologize for the confusion.

       

      What we’re trying to do is take the date diff of Shipping Date Confirmed & Delivery Date from Fact_SalesCogs, look at whether it was Direct Delivery or Warehouse & the threshold logic for those (in the DAX), and return On-time if it’s within the threshold of DD or WH.

       

      So example would be, IF Shipping Date Confirmed is NOTBLANK, take datedif(DeliveryDate, ShippingDateConfirmed,DAY)

      && ( Fact_SalesCogs[DropShippedInd] = 1

      && [Shipdays] <= 1

      && [Shipdays] >= 0

      || Fact_SalesCogs[DropShippedInd] = 0

      && [Shipdays] <= 3

      && [Shipdays] >= 0 )

       

      Something along those lines.

       

      mahoneypat AlB PhilipTreacy AllisonKennedy  tagging you guys, since I see each of you were Top Solution Authors last month. If you guys could help me, I would greatly appreciate it. Hope to clear up any questions you would have as well. Thank you!

      • AllisonKennedy's avatar
        AllisonKennedy
        Community Champion
        5 years ago

        ka047  It seems like you have a near solution from lbendlin . How many tables are you working with? 

         

        If you need an exact most efficient solutionm then please provide a sample table of data or at very least table and column names and any relationships. 

         

        Then if you can describe the logic succinctly in english (not with DAX), then we can suggest our best approaches from there. 🙂