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

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.