Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculated column based on column from another table

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_activitydate
122/09/2020
223/10/2020
303/11/2020
401/09/2020
501/10/2020

 

And another table history with this structure:

ID_groupID_activity
11
12
13
24
25

 

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_groupID_activitymost_recent
11False
12False
13True
24False
25True

 

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

2 ACCEPTED SOLUTIONS
AlB
Community Champion
Community Champion

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 

SU18_powerbi_badge

View solution in original post

V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

Calculated column based on column from another table.PNG

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.

View solution in original post

2 REPLIES 2
V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

Calculated column based on column from another table.PNG

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
Community Champion
Community Champion

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 

SU18_powerbi_badge

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.