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

 

 

 

pbrainard's avatar
pbrainard
Helper III
4 years ago

I got this far:

I added some sample data earlier, and my desired outcome.

  • SpartaBI's avatar
    SpartaBI
    Community Champion
    4 years ago

    pbrainard copy the code exactly as I wrote it 🙂 in the script box

    • pbrainard's avatar
      pbrainard
      Helper III
      4 years ago

      First thing that pops up is 'Unexpected Expression EVALUATE

      My field name is Assess_Type not Asset_Type so those are not recognized.

       

      • pbrainard's avatar
        pbrainard
        Helper III
        4 years ago

        It's running without an error now, but it's producing the same as before.

         

         

        This is what I need to see:


        Pulling from this data:

        Because the first GAD7 score is 12 and the last GAD7 score is 1. And the first PHQ9 score is 7 and the last one is 1.

    • pbrainard's avatar
      pbrainard
      Helper III
      4 years ago

      Changing the Asset to Assess...

       

       

      • SpartaBI's avatar
        SpartaBI
        Community Champion
        4 years ago

        pbrainard ok ok do that 🙂 and take the code from there. I will also change it in the script so you could mark that as an answer in case it works.