Forum Discussion
Solved
Add Filter to Calculated Table
I have a calculated table that pulls a Start Date (and score) and End Date (and score), but I neglected to add in that there are two different assessments (GAD and PHQ). Right now the results aren't ...
pbrainard had a typo, I'm writing this without testing on your data, cause I'm still not sure what is the outcome you want. I'm guessing 🙂 Try this:
CTab = ADDCOLUMNS ( ADDCOLUMNS ( SUMMARIZE ( GAD_PHQ, GAD_PHQ[Client_ID], GAD_PHQ[Assess_Type] ), "Start Date", CALCULATE ( MIN ( GAD_PHQ[Assess_Date] ) ), "End Date", CALCULATE ( MAX ( GAD_PHQ[Assess_Date] ) ) ), "Start Score", VAR _current_assess_type = GAD_PHQ[Assess_Type] VAR _current_start_date = [Start Date] RETURN CALCULATE ( AVERAGE ( GAD_PHQ[Assess_Score] ), GAD_PHQ[Assess_Date] = _current_start_date, GAD_PHQ[Assess_Type] = _current_assess_type ), "End Score", VAR _current_assess_type = GAD_PHQ[Assess_Type] VAR _current_end_date = [End Date] RETURN CALCULATE ( AVERAGE ( GAD_PHQ[Assess_Score] ), GAD_PHQ[Assess_Date] = _current_end_date, GAD_PHQ[Assess_Type] = _current_assess_type ) )
CTab = ADDCOLUMNS ( SUMMARIZECOLUMNS ( GAD_PHQ[Client_ID], "Start Date", MIN ( GAD_PHQ[Assess_Date] ), "End Date", MAX ( GAD_PHQ[Assess_Date] ) ), "Start Score", CALCULATE ( AVERAGE( GAD_PHQ[Assess_Score] ), GAD_PHQ[Assess_Date] = EARLIER( [Start Date] ) ), "End Score", CALCULATE ( AVERAGE ( GAD_PHQ[Assess_Score] ), GAD_PHQ[Assess_Date] = EARLIER ( [End Date] ) ) )
The above statement works. I just want to be able to get the first date and score and last date and score by Client ID, by Assess Type. I've included some sample data below. Looking at Client ID = 11236. There are a bunch of GAD7 and a bunch of PHQ9. I would like to see a single line for each Client ID that has the GAD7 MIN date and score and MAX date and score. Same for the PHQ9. The above DAX is currently working but only for each Client ID, does not account for the Assess Type.
| Client_ID | Assess_Type | Assess_Date | Assess_Score |
| 19146 | PHQ9 | 10/14/2021 | 21 |
| 18565 | GAD7 | 5/26/2020 | 6 |
| 18565 | PHQ9 | 5/26/2020 | 14 |
| 16994 | GAD7 | 11/9/2021 | 11 |
| 16994 | GAD7 | 1/14/2022 | 16 |
| 18837 | PHQ9 | 1/15/2020 | 17 |
| 19293 | PHQ9 | 7/27/2021 | 8 |
| 11236 | GAD7 | 2/8/2021 | 12 |
| 11236 | GAD7 | 3/15/2021 | 8 |
| 11236 | GAD7 | 10/4/2021 | 5 |
| 11236 | GAD7 | 10/18/2021 | 3 |
| 11236 | GAD7 | 11/8/2021 | 7 |
| 11236 | GAD7 | 11/29/2021 | 5 |
| 11236 | GAD7 | 12/13/2021 | 8 |
| 11236 | GAD7 | 1/24/2022 | 14 |
| 11236 | GAD7 | 3/7/2022 | 12 |
| 11236 | GAD7 | 3/21/2022 | 1 |
| 11236 | PHQ9 | 2/8/2021 | 7 |
| 11236 | PHQ9 | 3/15/2021 | 9 |
| 11236 | PHQ9 | 10/4/2021 | 1 |
| 11236 | PHQ9 | 10/18/2021 | 0 |
| 11236 | PHQ9 | 11/8/2021 | 5 |
| 11236 | PHQ9 | 11/29/2021 | 2 |
| 11236 | PHQ9 | 12/13/2021 | 1 |
| 11236 | PHQ9 | 1/24/2022 | 9 |
| 11236 | PHQ9 | 3/7/2022 | 9 |
| 11236 | PHQ9 | 3/21/2022 | 1 |
This is some sample data of what the above DAX is producing.
| Client_ID | Start Date | End Date | Start Score | End Score |
| 10075 | 1/19/2022 | 1/19/2022 | 4 | 11 |
| 10302 | 6/4/2020 | 3/3/2022 | 8 | 16 |
| 10350 | 6/5/2020 | 3/4/2022 | 15 | 13 |
| 11236 | 2/8/2021 | 3/21/2022 | 7 | 1 |
| 11380 | 2/7/2020 | 1/18/2022 | 18 | 18 |
| 11662 | 11/12/2021 | 3/4/2022 | 16 | 15 |
| 11670 | 4/12/2021 | 3/21/2022 | 0 | 7 |
| 11692 | 2/7/2022 | 2/7/2022 | 16 | 10 |
| 13285 | 7/20/2021 | 1/19/2022 | 8 | 15 |
| 13347 | 10/7/2020 | 3/16/2022 | 8 | 5 |
| 13608 | 6/5/2020 | 2/3/2022 | 11 | 19 |
| 13652 | 3/24/2021 | 3/17/2022 | 14 | 16 |
| 16297 | 7/27/2021 | 3/1/2022 | 11 | 4 |