Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.

Need Help
Show and Tell
Tips and Tricks
  • AlB's avatar
    AlB
    5 years ago

    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's avatar
    V-lianl-msft
    5 years ago

    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.

2 Replies

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    5 years ago

    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's avatar
    AlB
    Community Champion
    5 years ago

    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