topic Re: Calculate if order is too late or on time based on parameter in DAX Commands and Tips

Calculate if order is too late or on time based on parameter

RegionH — Mon, 18 Mar 2024 14:15:46 GMT

My datamodel has two tables called 'Indkøbsordrer' and 'Varemodtagelser'. The two tables are connected on [Ordre nøgle] in a one to many relationship.

I'm trying to calculate if an order is "Too late" or "On time" based on the following DAX-formula.

Ordrer Punktlighed = if( ISEMPTY( Filter( CALCULATETABLE( 'Varemodtagelser', ALLEXCEPT('Varemodtagelser','Varemodtagelser'[Indkøbsordrenummer])), sum(Varemodtagelser[Modtagelsesdato])-'Leveringstidsjustering'[Leveringstidsjustering Value]-sum(Varemodtagelser[Statistikdato]) > 0 ) ), "On time", "Late" )

The formula is working fine. But now I would like to distinguish between the value of the column 'Indkøbsordrer'[Indkøbsbilagsart]. If the value in the row is equal to "ZLM" or "ZCD, then use the parameter as is. But if the value is diffent, then use the value 5 days.

So I wrote this formula, but it is not working, because when I use this measure in another measure to calculate the sum of "Delayed orders" I get 2.484. The same result as the original formula.

Z_Ordrer_Punktlighed_Bilagsart = IF( ISEMPTY( FILTER( CALCULATETABLE( 'Varemodtagelser', ALLEXCEPT('Varemodtagelser', 'Varemodtagelser'[Indkøbsordrenummer]) ), SUM('Varemodtagelser'[Modtagelsesdato]) - IF( RELATED('Indkøbsordrer'[Indkøbsbilagsart]) = "ZLM" || RELATED('Indkøbsordrer'[Indkøbsbilagsart]) = "ZCD", 'Leveringstidsjustering'[Leveringstidsjustering Value], 5 ) - SUM('Varemodtagelser'[Statistikdato]) > 0 ) ), "On time", "Late" )

I hope someone will take a look at the formula and maybe help me correct it?

Thanks in advance.

Best regards

Morten

Re: Calculate if order is too late or on time based on parameter

lbendlin — Mon, 18 Mar 2024 19:14:06 GMT

what's your reasoning for using RELATED in a measure?

Z_Ordrer_Punktlighed_Bilagsart = IF( ISEMPTY( FILTER( CALCULATETABLE( 'Varemodtagelser', ALLEXCEPT('Varemodtagelser', 'Varemodtagelser'[Indkøbsordrenummer]) ), SUM('Varemodtagelser'[Modtagelsesdato]) - IF('Indkøbsordrer'[Indkøbsbilagsart]) IN { "ZLM" ,"ZCD" }, 'Leveringstidsjustering'[Leveringstidsjustering Value],5 ) - SUM('Varemodtagelser'[Statistikdato]) > 0 ) ), "On time", "Late" )

You could also use Countrows instead of IF(ISEMPTY).

Re: Calculate if order is too late or on time based on parameter

RegionH — Tue, 19 Mar 2024 19:14:37 GMT

Thank you for answering my question. The reason for using related is that the table 'Indkøbsordrer' has the information about [Indkøbsbilagsart]. This information is not represented in the table 'Varemodtagelser'.

I wrote another DAX-formula which solved my problem.

Z_ForsinkedeBilagsarter = VAR Forsinkelser_ZLM_ZCD = CALCULATE ( DISTINCTCOUNT ( 'Indkøbsordrer'[Indkøbsordrenummer] ), FILTER ( 'Varemodtagelser', ( 'Varemodtagelser'[Modtagelsesdato] - 'Leveringstidsjustering'[Leveringstidsjustering Value] - 'Varemodtagelser'[Statistikdato] ) > 0 && ( RELATED ( 'Indkøbsordrer'[Indkøbsbilagsart] ) = "ZLM" || RELATED ( 'Indkøbsordrer'[Indkøbsbilagsart] ) = "ZCD" ) ) ) VAR Forsinkelser_Andre = CALCULATE ( DISTINCTCOUNT ( 'Indkøbsordrer'[Indkøbsordrenummer] ), FILTER ( 'Varemodtagelser', ( 'Varemodtagelser'[Modtagelsesdato] - 5 - 'Varemodtagelser'[Statistikdato] ) > 0 && NOT ( RELATED ( 'Indkøbsordrer'[Indkøbsbilagsart] ) = "ZLM" || RELATED ( 'Indkøbsordrer'[Indkøbsbilagsart] ) = "ZCD" ) ) ) RETURN Forsinkelser_ZLM_ZCD + Forsinkelser_Andre