Calculated column based on column from another table

[Anonymous]

Hi, 

I'm a newbie in PowerBI and I'm starting to  work on DAX functions.

I have a table activity with this structure: 

ID_activity	date
1	22/09/2020
2	23/10/2020
3	03/11/2020
4	01/09/2020
5	01/10/2020

And another table history with this structure:

ID_group	ID_activity
1	1
1	2
1	3
2	4
2	5

Basically, I need to know the most recent date for an activity in each group, knowing that the field I need to lookup to get the most recent date (date) is in another table  (activity table)

What I want to get is, in history table, a calculated column that returns me True in case an activity for a group has the most recent date. So my desired result is the following, where row with ID_group=1 and ID_activity=3 has most_recent=True because activity 3 is the most recent activity that group 1 has done. 

ID_group	ID_activity	most_recent
1	1	False
1	2	False
1	3	True
2	4	False
2	5	True

Which function would I have to use to achieve this? Many thanks  for your help.

[AlB · Accepted solution]

Hi @Anonymous 

Most_recent =
VAR actsInGrp_ =
    CALCULATETABLE (
        DISTINCT ( History[ID_activity] ),
        ALLEXCEPT ( History, History[ID_Group] )
    )
VAR current_ =
    CALCULATE (
        MAX ( Activity[date] ),
        FILTER (
            ALL ( Activity[ID_activity] ),
            Activity[ID_activity] = History[ID_activity]
        )
    )
VAR maxInGrp_ =
    MAXX (
        FILTER ( Activity, Activity[ID_activity] IN actsInGrp_ ),
        Activity[date]
    )
RETURN
    current_ = maxInGrp_

This can also be done in Power query (probably better) and it can be simplified in DAX if you can create a relationship betweenthe two tables

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[V-lianl-msft · Accepted solution]

Hi @Anonymous ,

If there is such a relationship between activity table and history table, try the following DAX statement.

most_recent =
VAR _date =
    RELATED ( activity[date] )
VAR recent =
    CALCULATE (
        MAX ( activity[date] ),
        ALLEXCEPT (
            history,
            history[ID_group]
        )
    )
RETURN
    IF (
        _date = recent,
        TRUE (),
        FALSE ()

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlB · Accepted solution]

Hi @Anonymous 

Most_recent =
VAR actsInGrp_ =
    CALCULATETABLE (
        DISTINCT ( History[ID_activity] ),
        ALLEXCEPT ( History, History[ID_Group] )
    )
VAR current_ =
    CALCULATE (
        MAX ( Activity[date] ),
        FILTER (
            ALL ( Activity[ID_activity] ),
            Activity[ID_activity] = History[ID_activity]
        )
    )
VAR maxInGrp_ =
    MAXX (
        FILTER ( Activity, Activity[ID_activity] IN actsInGrp_ ),
        Activity[date]
    )
RETURN
    current_ = maxInGrp_

This can also be done in Power query (probably better) and it can be simplified in DAX if you can create a relationship betweenthe two tables

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers