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.

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