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 pulling the first and last scores per Assessment Type. How do I change the table calc to give me first date and score, and last date and score, per assessment 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])))
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 ) )
15 Replies
pbrainard you should never wrap anything outside SUMMARIZECOLUMNS.
In case you use SUMMARIZECOLUMNS this should be the most outer function.
Better if you shared your data as an example in a the table you have and the table you want to achieve, but I took a guess this is what you meant:CTab = ADDCOLUMNS( ADDCOLUMNS ( VALUES(GAD_PHQ[Client_ID]), "Start Date", CALCULATE(MIN ( GAD_PHQ[Assess_Date] )), "End Date", CALCULATE(MAX ( GAD_PHQ[Assess_Date] )) ), "Start Score", CALCULATE ( AVERAGE ( GAD_PHQ[Assess_Score] ), GAD_PHQ[Assess_Date] = [Start Date] ), "End Score", CALCULATE ( AVERAGE ( GAD_PHQ[Assess_Score] ), GAD_PHQ[Assess_Date] = [End Date] ) ) )- 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
pbrainard Try this:
CTab = ADDCOLUMNS( ADDCOLUMNS ( SUMMARIZE( GAD_PHQ, GAD_PHQ[Client_ID], GAD_PHQ[Asset_Type] ), "Start Date", CALCULATE(MIN ( GAD_PHQ[Assess_Date] )), "End Date", CALCULATE(MAX ( GAD_PHQ[Assess_Date] )) ), VAR _current_asset_type = GAD_PHQ[Asset_Type] "Start Score", CALCULATE ( AVERAGE ( GAD_PHQ[Assess_Score] ), GAD_PHQ[Assess_Date] = [Start Date], GAD_PHQ[Asset_Type] = _current_asset_type ), "End Score", CALCULATE ( AVERAGE ( GAD_PHQ[Assess_Score] ), GAD_PHQ[Assess_Date] = [End Date], GAD_PHQ[Asset_Type] = _current_asset_type ) ) )This is what I'm getting:
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 ) )
