Calculated Column

[ThijsvdZaan]

Hey there,

I am very new in PowerBI and I could use some help with this (hopefully) simple task. 

I have 2 tables. 

Table 1: Activities

Large table with many columns. Most important for now is the 'start' column, with the date of the record submission, and the 'Village' column indicating which village was visited. There will be multiple records in the table for each village. 

Table 2: Villages

A table with a number of village names. Each village will be in here only once. 

What I'm trying to do, is to find the most recent record (visit) for each village in the activity table. I thought I'd calculate a new column in the Village table, and fetch the most recent record for that village from the Activity table, but I can't figure out how to do this. Note that it can be possible that some villages are never visited, so then then won't be found in any of the records in the Activity table. 

Hopefully someone can help me!

Thanks in advance!

[bhanu_gautam · Accepted solution]

@ThijsvdZaan , In the Villages table, create a new calculated column to find the most recent visit date for each village. You can do this by using the following DAX formula:

DAX
MostRecentVisit =
IF(
ISBLANK(
CALCULATE(
MAX(Activities[start]),
FILTER(
Activities,
Activities[Village] = Villages[Village]
)
)
),
"No Visits",
CALCULATE(
MAX(Activities[start]),
FILTER(
Activities,
Activities[Village] = Villages[Village]
)
)
)

[ThijsvdZaan]

That worked just fine, thank you!!