Help Needed: Creating new column to count matching values - (for example, a teacher grading a quiz)

[mikec007]

Hi! I have a Power App that collects responses from users (similiar to a student taking a quiz/test).  Their submissions are stored in a dataverse, one entry = one row of data.  The data produces a table that looks similar to the following.

NOTES:

1. In this example, Johnny and Sam have made selections for two weeks worth of "quizzes" and therefor have two rows of data.  (Think of these rows like the responses to test questions).
2. “admin_picks” are the selections I would put in for each week's "quiz". (think of these rows as the quiz's answer keys).
3. Each row where a user's YEAR and WEEK match the "admin_picks" YEAR and WEEK would compare values and count matches.

I am trying to find a method to programmatically determine Johnny and Sam’s scores by comparing them to the corresponding row “admin_picks” (WHERE 'year' and 'week' would be the same 'year' and 'week' as Johnny and Sam's answers submissions).  I could then rank users based on weekly correct responses and grand totals.

The output would look like the following table (Ideally, the DAX/Report code would be configured in such a way where I could simply refresh the data and the table below would be dynamically updated based on any new entries in the dataverse.

Year	Name	score_week1	score_week2	grand_total
2022	Sam	6	6	12
2022	Johnny	6	5	11

Warning:  I am an App maker and not proficient in DAX (I'm just learning PowerBI at this point, but have a background in SSRS and SQL query), so I may be out of my league a bit, but I feel like my background gets me close logically.  If I had some advice/guidance to help push me along, I might be able to achieve this.  Thanks for reading and any guidance/help is appreciated! -Mike

[v-rongtiep-msft · Accepted solution]

Hi @mikec007 ,

I have created a simple sample, please refer to it to see if it helps you.

Click Home>>Transform data>>Transform data.

select P1-P10, then click Transform>>unpivot columns.

Finally create two measures.

Measure =
VAR _admin =
    CALCULATE (
        MAX ( 'Table _COPY'[Value] ),
        FILTER (
            ALL ( 'Table _COPY' ),
            'Table _COPY'[name] = "admin_picks"
                && 'Table _COPY'[Year] = SELECTEDVALUE ( 'Table _COPY'[Year] )
                && 'Table _COPY'[Week] = SELECTEDVALUE ( 'Table _COPY'[Week] )
                && 'Table _COPY'[pre] = SELECTEDVALUE ( 'Table _COPY'[pre] )
        )
    )
RETURN
    IF ( MAX ( 'Table _COPY'[Value] ) = _admin, 1, 0 )

Measure_2 =
COUNTAX (
    FILTER (
        ALL ( 'Table _COPY' ),
        'Table _COPY'[name] = SELECTEDVALUE ( 'Table _COPY'[name] )
            && 'Table _COPY'[Year] = SELECTEDVALUE ( 'Table _COPY'[Year] )
            && 'Table _COPY'[Week] = SELECTEDVALUE ( 'Table _COPY'[Week] )
            && [Measure] = 1
    ),
    [Measure]
)

Best Regards
Community Support Team _ Polly

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

[mikec007]

Thank you!  I wanted to let you know I'm still working with this possibly solution.  I will provide an update when I have better feedback from my testing.  Your assistance is much appreciated!

[mikec007]

This was the stepping stone to exactly what we were trying to achieve.  Thank you very much for this guidance!