Forum Discussion

mikec007's avatar
mikec007
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago
Solved

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

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

Quiz
Student
Teacher
Test
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      mikec007
      Icon for Microsoft Employee rankMicrosoft Employee
      4 years ago

      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's avatar
        mikec007
        Icon for Microsoft Employee rankMicrosoft Employee
        4 years ago

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