Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I am fairly new to Power BI and trying to understand the DAX funtions. I am struck with the below. Any help is appreciated.
The request is to get the first status based on the start date in dataset2 where parent id matches and start date of data set2 is with in 31 days of startdate of dataset1
Solved! Go to Solution.
Hi @powerbi_9
You may refer to below dax to create the calculated columns and link the two table in relationship view.
date_outcome = CALCULATE ( FIRSTNONBLANK ( Table2[StartDate], 1 ), FILTER ( Table2, Table2[StartDate] >= Table1[StartDate] && Table2[ParentID] = Table1[ParentID] && Table2[StartDate] < Table1[StartDate] + 31 ) )
Status_lookup = CALCULATE ( FIRSTNONBLANK ( Table2[Status], 1 ), TOPN ( 1, FILTER ( Table2, Table2[StartDate] = Table1[date_outcome] && Table2[ParentID] = Table1[ParentID] ), Table2[ChildID], ASC ) )
Regards,
Cherie
Also, it is possible that both datasets may have the duplicate parent id's.
Hi @powerbi_9
You may refer to below dax to create the calculated columns and link the two table in relationship view.
date_outcome = CALCULATE ( FIRSTNONBLANK ( Table2[StartDate], 1 ), FILTER ( Table2, Table2[StartDate] >= Table1[StartDate] && Table2[ParentID] = Table1[ParentID] && Table2[StartDate] < Table1[StartDate] + 31 ) )
Status_lookup = CALCULATE ( FIRSTNONBLANK ( Table2[Status], 1 ), TOPN ( 1, FILTER ( Table2, Table2[StartDate] = Table1[date_outcome] && Table2[ParentID] = Table1[ParentID] ), Table2[ChildID], ASC ) )
Regards,
Cherie
Worked perfectly. Thank you!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |