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

[RegionH]

Hi

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  

[lbendlin]

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

[RegionH · Accepted solution]

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