Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I want to get the active child record in one to many relationship. Below is the relationship.
Projects (One)
Tasks (Many)
e.g.
Attaching here the PBIX file as well.
Solved! Go to Solution.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
@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
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.