Measure if value in 1st table , then specific calculation in 2nd

Question

Looking for your expertise. Stuck on the 1 measure.

Given: I have 2 tables.

• Table 1 contains unique country name and status ("running" or "completed").
• Table 2 contains facts country name (with repetitions) and dates (created and modified) I need to use

Idea is to have measure that will show:

If in Table 1:

• Country, status = Running, then Table 2: date today - min created
• Country, status = Completed, then Table 2: max modified - min created

I came up with following dax, but it is not working

CampaignDuration = 

var running_days = -DATEDIFF(TODAY(),MIN('Table2'[Created]),WEEK)

var closed_days = -DATEDIFF(MAX('COI_Merged_All'[Modified]),MIN('Table2'[Created]),WEEK)

VAR Campaign_Status = MAX('Table1'[status])

var result = 
IF(Campaign_Status = "Completed", 
"completed in" & " " & closed_days & " " & "weeks", 
"ongoing" & " " & running_days & " " & "weeks")

RETURN

result

Many thanks in advance for your support

Table 1 | Country | Status | | ------- | --------- | | Spain | Running | | UK | Completed |

Table 2 | Country | Created | Modified | | ------- | ------------- | ----------- | | Spain | 01.01.2024 | 04.01.2024 | | Spain | 02.01.2024 | 05.01.2024 | | Spain | 03.01.2024 | 06.01.2024 | | UK | 01.01.2024 | 03.01.2024 | | UK | 02.01.2024 | 04.01.2024 |

Today - 10.01.2024

Expected result:

1. Spain selected - 10.01.2024 - 01.01.2024 = 9 days campaign ongoing
2. UK selected - 04.01.2024 - 01.01.2024 = done in 3 days

Answer 1

you can try to create relationship between two tables

then create a measure

MEASURE =
IF (
    MAX ( 'Table 1'[Status] ) = "Running",
    DATEDIFF ( MIN ( 'Table 2'[Created] ), DATE ( 2024, 1, 10 ), DAY ),
    IF (
        MAX ( 'Table 1'[Status] ) = "Completed",
        DATEDIFF ( MIN ( 'Table 2'[Created] ), MAX ( 'Table 2'[Modified] ), DAY )
    )
)

in your real data, you can change DATE ( 2024, 1, 10 ) to today()