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 ) )
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
)
)