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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
marypal
Frequent Visitor

How to find the last value for each group in a reporting period and count the average for these rows

Hello,

 

Could anybody help me with my task, please.

I am using a DirectQuery mode.
My Table 1 contains columns:
- activity_type

- client_id

- activity_id

- activity_date

- score
I have a reporting date, the last date of a month, that we choose using a slicer. 
I need to find for each client_id, activity_type the last activity_id and corresponding score such that activity_date<=reporting_date and display that in Table 2.
After that we need to count for each activity_type the number of clients for which score is not null and find the average score.
Would be greatful for any hints

marypal_0-1694686371617.png

 

Many thanks!

 

 

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @marypal ,

 

Please try code as below to create measures.

Avg Score = 
VAR _ReportDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _GENERATE =
    GENERATE (
        VALUES ( 'Table 1'[activity_type] ),
        VALUES ( 'Table 1'[client_id] )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _GENERATE,
        "Score",
            VAR _MAXDATE =
                CALCULATE (
                    MAX ( 'Table 1'[activity_date] ),
                    FILTER (
                        'Table 1',
                        'Table 1'[activity_type] = EARLIER ( [activity_type] )
                            && 'Table 1'[client_id] = EARLIER ( [client_id] )
                            && 'Table 1'[activity_date] <= _ReportDate
                    )
                )
            RETURN
                CALCULATE (
                    SUM ( 'Table 1'[score] ),
                    FILTER (
                        'Table 1',
                        'Table 1'[activity_type] = EARLIER ( [activity_type] )
                            && 'Table 1'[client_id] = EARLIER ( [client_id] )
                            && 'Table 1'[activity_date] = _MAXDATE
                    )
                )
    )
RETURN
    AVERAGEX (
        FILTER ( _ADD1, [activity_type] = MAX ( 'Table 1'[activity_type] ) ),
        [Score]
    )
Number of Clients = 
VAR _ReportDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _GENERATE =
    GENERATE (
        VALUES ( 'Table 1'[activity_type] ),
        VALUES ( 'Table 1'[client_id] )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _GENERATE,
        "Score",
            VAR _MAXDATE =
                CALCULATE (
                    MAX ( 'Table 1'[activity_date] ),
                    FILTER (
                        'Table 1',
                        'Table 1'[activity_type] = EARLIER ( [activity_type] )
                            && 'Table 1'[client_id] = EARLIER ( [client_id] )
                            && 'Table 1'[activity_date] <= _ReportDate
                    )
                )
            RETURN
                CALCULATE (
                    SUM ( 'Table 1'[score] ),
                    FILTER (
                        'Table 1',
                        'Table 1'[activity_type] = EARLIER ( [activity_type] )
                            && 'Table 1'[client_id] = EARLIER ( [client_id] )
                            && 'Table 1'[activity_date] = _MAXDATE
                    )
                )
    )
RETURN
COUNTX(FILTER(_ADD1,[activity_type] = MAX('Table 1'[activity_type]) && [Score]<>BLANK()),[activity_type])

Result is as below.

vrzhoumsft_0-1695203360948.png

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

Hi @marypal ,

 

Please try code as below to create measures.

Avg Score = 
VAR _ReportDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _GENERATE =
    GENERATE (
        VALUES ( 'Table 1'[activity_type] ),
        VALUES ( 'Table 1'[client_id] )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _GENERATE,
        "Score",
            VAR _MAXDATE =
                CALCULATE (
                    MAX ( 'Table 1'[activity_date] ),
                    FILTER (
                        'Table 1',
                        'Table 1'[activity_type] = EARLIER ( [activity_type] )
                            && 'Table 1'[client_id] = EARLIER ( [client_id] )
                            && 'Table 1'[activity_date] <= _ReportDate
                    )
                )
            RETURN
                CALCULATE (
                    SUM ( 'Table 1'[score] ),
                    FILTER (
                        'Table 1',
                        'Table 1'[activity_type] = EARLIER ( [activity_type] )
                            && 'Table 1'[client_id] = EARLIER ( [client_id] )
                            && 'Table 1'[activity_date] = _MAXDATE
                    )
                )
    )
RETURN
    AVERAGEX (
        FILTER ( _ADD1, [activity_type] = MAX ( 'Table 1'[activity_type] ) ),
        [Score]
    )
Number of Clients = 
VAR _ReportDate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _GENERATE =
    GENERATE (
        VALUES ( 'Table 1'[activity_type] ),
        VALUES ( 'Table 1'[client_id] )
    )
VAR _ADD1 =
    ADDCOLUMNS (
        _GENERATE,
        "Score",
            VAR _MAXDATE =
                CALCULATE (
                    MAX ( 'Table 1'[activity_date] ),
                    FILTER (
                        'Table 1',
                        'Table 1'[activity_type] = EARLIER ( [activity_type] )
                            && 'Table 1'[client_id] = EARLIER ( [client_id] )
                            && 'Table 1'[activity_date] <= _ReportDate
                    )
                )
            RETURN
                CALCULATE (
                    SUM ( 'Table 1'[score] ),
                    FILTER (
                        'Table 1',
                        'Table 1'[activity_type] = EARLIER ( [activity_type] )
                            && 'Table 1'[client_id] = EARLIER ( [client_id] )
                            && 'Table 1'[activity_date] = _MAXDATE
                    )
                )
    )
RETURN
COUNTX(FILTER(_ADD1,[activity_type] = MAX('Table 1'[activity_type]) && [Score]<>BLANK()),[activity_type])

Result is as below.

vrzhoumsft_0-1695203360948.png

 

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Thank you very much, Rico!

lbendlin
Super User
Super User

I need to find for each client_id, activity_type the last activity_id and corresponding score

The max([activity _id]) is not a problem.  However the "corresponding score"  is, as you cannot easily aggregate that.

 

You will likely need to use table variables  in your measure and filter that table variable.  Not sure how feasible that is in Direct Query mode.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.