Dynamic date substitution

Hi, implementing write back with PowerApps have two tables one with order number, amount and requested delivery dates (table 1) another with order number and confirmed delivery date (table 2). Table...

DAX Direct Query

PowerApp write back

V-yubandi-msft
1 year ago
Hi dimotori ,

I’ve included the measure as you recommended and attached the file. Please review it when you have a chance and let me know if everything is correct.

Hope this is helpful.

4731791 1.pbix3.2 MB

Nasif_Azam

Super User

1 year ago

Hey dimotori ,

It seems that the filter expression you're using in the measure might be causing issues due to the relationship and how the tables are connected. Since you're using a 1:* relationship between Table1 (import mode) and Table2 (direct query mode), this could lead to problems when trying to reference fields from Table2 in the filter of Table1. Updated approach you can try:

Ensure Correct Relationship: Make sure that the relationship between your tables is properly set. It should be based on OrderNumber for both tables. If your ConfirmedDeliveryDate is in Table2 and RequestedDeliveryDate is in Table1, you should be able to perform the check in your measure correctly.
Update the Measure to Avoid Direct Filtering:
Instead of using a FILTER directly on Table1 inside your measure, you can make use of TREATAS to pass the filter from the calendar table to Table2, ensuring you're using the correct date context.

DynamicDeliveryAmount = 
VAR CurrentDate = MAX('Calendar'[Date])
VAR Confirmed =
    CALCULATE(
        MAX(Table2[ConfirmedDeliveryDate]),
        TREATAS(
            VALUES(Table1[OrderNumber]),
            Table2[OrderNumber]
        )
    )
VAR ChosenDate =
    IF(NOT ISBLANK(Confirmed), Confirmed, MAX(Table1[RequestedDeliveryDate]))
RETURN
    CALCULATE(
        SUM(Table1[Amount]),
        FILTER(
            Table1,
            ChosenDate = CurrentDate
        )
    )

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam