Add Filter to Calculated Table

[pbrainard]

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

[SpartaBI]

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

[pbrainard]

This is what I'm getting:

[SpartaBI · Accepted solution]

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

I got this far:

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

[SpartaBI]

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

[pbrainard]

Changing the Asset to Assess...

[SpartaBI]

@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.

[SpartaBI]

@pbrainard so?

You got it? 

[pbrainard]

It appears to be working. I am going to go with it!!! Thank you so much for your patience and diligence.

[pbrainard]

I'm vetting the results.

[pbrainard]

First thing that pops up is 'Unexpected Expression EVALUATE

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

[pbrainard]

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]

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

[pbrainard]

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

[SpartaBI]

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