How to get active child record from one to many relationship in DAX

[ankitkalsara]

Hi all,

I want to get the active child record in one to many relationship. Below is the relationship.

Projects (One)

Tasks (Many)

• For each Projects, we need to know which Task is active as of today.
• If there are no active records as of today, then get the next task record.

e.g.

• Project 1 - "Task 2" is active (since Start Date and End Date falls between today i.e. 10th May 2022)
• Project 2 - "Task 3" is active (since there is no task which is active as of today and thus we need to get next task)

Attaching here the PBIX file as well.

[v-yalanwu-msft]

Hi @ankitkalsara ;

Did amitchandak solve your problem? I think his scheme is correct, or you can also use the following measure

Active Name = 
VAR _count =
    CALCULATE (
        COUNT ( [Task Id] ),
        FILTER (
            ALLEXCEPT ( 'Tasks', 'Tasks'[Project Id] ),
            [Start Date] <= TODAY ()
                && [End Date] >= TODAY ()) )
RETURN
    IF (
        TODAY () <= MAX ( 'Tasks'[End Date] )
            && TODAY () >= MAX ( 'Tasks'[Start Date] ),
        MAX('Tasks'[Task Name]),
        IF (
            _count < 1&&
         MAX ( 'Tasks'[Start Date] )
                    IN SUMMARIZE (
                        TOPN (
                            1,
                            FILTER ( ALLEXCEPT ( 'Tasks', 'Tasks'[Project Id] ), [Start Date] > TODAY () ),
                            [Start Date], ASC
                        ),
                        [Start Date]),
                        MAX([Task Name])))

The result as follow:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CNENFRNL · Accepted solution]

[ankitkalsara]

Hi @CNENFRNL , thank you for the solution 🙂

[ankitkalsara]

Hi @amitchandak :

The above code is not working for large datasets, do you know any other way to get the results?

Appreciate your help on this

[amitchandak]

@ankitkalsara , Create a measure like this and use in visual or use visual level filter

countrows(filter(Task, Task[Start Date]<=Today() && Table[End date] >= Today() ) )

also check

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[ankitkalsara]

Hi @amitchandak, 

Thank you for your reply.

The measure gives the number of rows which are active. However, I want to get the row details which is active as of today and if not then the next row.

I tried the below code and it seems to be working for now. I created calculated column.