Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pbrainard
Helper III
Helper III

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

 

 

 

View solution in original post

15 REPLIES 15
SpartaBI
Community Champion
Community Champion

@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_0-1651421384408.png

 

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

 

 

 

I got this far:

pbrainard_1-1651423487675.png

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

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

Changing the Asset to Assess...

 

pbrainard_1-1651424086378.png

 

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

@pbrainard so?

You got it? 

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

I'm vetting the results.

 

First thing that pops up is 'Unexpected Expression EVALUATE

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

pbrainard_0-1651423964070.png

 

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

pbrainard_4-1651425414197.png

 

 

This is what I need to see:

pbrainard_2-1651425258766.png


Pulling from this data:

pbrainard_3-1651425327469.png

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
Helper III
Helper III

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_IDAssess_TypeAssess_DateAssess_Score
19146PHQ910/14/202121
18565GAD75/26/20206
18565PHQ95/26/202014
16994GAD711/9/202111
16994GAD71/14/202216
18837PHQ91/15/202017
19293PHQ97/27/20218
11236GAD72/8/202112
11236GAD73/15/20218
11236GAD710/4/20215
11236GAD710/18/20213
11236GAD711/8/20217
11236GAD711/29/20215
11236GAD712/13/20218
11236GAD71/24/202214
11236GAD73/7/202212
11236GAD73/21/20221
11236PHQ92/8/20217
11236PHQ93/15/20219
11236PHQ910/4/20211
11236PHQ910/18/20210
11236PHQ911/8/20215
11236PHQ911/29/20212
11236PHQ912/13/20211
11236PHQ91/24/20229
11236PHQ93/7/20229
11236PHQ93/21/20221
 
 

This is some sample data of what the above DAX is producing.

Client_IDStart DateEnd DateStart ScoreEnd Score
100751/19/20221/19/2022411
103026/4/20203/3/2022816
103506/5/20203/4/20221513
112362/8/20213/21/202271
113802/7/20201/18/20221818
1166211/12/20213/4/20221615
116704/12/20213/21/202207
116922/7/20222/7/20221610
132857/20/20211/19/2022815
1334710/7/20203/16/202285
136086/5/20202/3/20221119
136523/24/20213/17/20221416
162977/27/20213/1/2022114
SpartaBI
Community Champion
Community Champion

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.