DAX Measure help - First Post

[ka047]

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

)

)

[lbendlin]

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]

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]

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

[amitchandak]

@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]

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]

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]

@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 · Accepted solution]

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]

instead of AND() you can use &&.

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

for OR() you can use ||

[ka047]

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]

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

)

)