Forum Discussion

pbrainard's avatar
pbrainard
Helper III
4 years ago
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 ...
Need Help
  • SpartaBI's avatar
    SpartaBI
    4 years ago

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

     

     

     

SpartaBI's avatar
SpartaBI
Community Champion
4 years ago

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