Group By Won't Work - How to aggregate?

[theitguy]

Hi guys,

What I have:

Learning Sessions:

Participant_ID	Unique_Test_ID	Single_Session_ID	Launch_Date	Seconds_Spend	Status	Score
1	t_01	20984	17-11-2017	444	incomplete	0
1	t_01	23271	17-11-2017	24	incomplete	0
1	t_01	23272	17-11-2017	123	complete	12
1	t_02	23273	19-11-2017	974	complete	36
4	t_03	20970	23-11-2017	439	incomplete	0
4	t_04	23301	23-11-2017	1	complete	66
5	t_05	20935	05-11-2017	84	incomplete	0
5	t_06	21819	05-11-2017	28	incomplete	0
5	t_05	21817	17-11-2017	72	incomplete	0
5	t_06	20983	17-11-2017	373	complete	80

Participants:

Participant_ID	Name	DepartmentCode	Hire_Date
1	John Doe	12345	01-11-2017
2	Max Mustermann	12345	01-11-2017
3	John Smith	12345	01-11-2017
4	Jan Janssen	12345	01-11-2017
5	John Blow	123456	01-11-2017

Want I want:

• Measure1: For each test of a participant, I want the sum of [Seconds_Spend], e.g. for participant "1" and test with id "t_01" I want "591"
• Measure2: For each test of a participant, I want the last entry of [Score], e.g. for participant "1" and test with id "t_01" I want to get "12"

Unfortunately, I cannot use GroupBy because multiple columns cannot be converted to a scalar value. Do I have to change my database design to achieve what I want?

[v-yulgu-msft]

Hi @theitguy,

---

• Measure1: For each test of a participant, I want the sum of [Seconds_Spend], e.g. for participant "1" and test with id "t_01" I want "591"
• Measure2: For each test of a participant, I want the last entry of [Score], e.g. for participant "1" and test with id "t_01" I want to get "12" 

I am not sure how to determine the "last entry of [Score]", so in my test, I added an Index column in Query Editor mode, each time, I returned the last row (max index number) per Participant_ID per Unique_Test_ID.

Please refer to below measure formulas:

Measure1 =
CALCULATE (
    SUM ( 'Learning Sessions'[Seconds_Spend] ),
    ALLEXCEPT (
        'Learning Sessions',
        'Learning Sessions'[Participant_ID],
        'Learning Sessions'[Unique_Test_ID]
    )
)

Measure2 =
CALCULATE (
    SUM ( 'Learning Sessions'[Score] ),
    FILTER (
        'Learning Sessions',
        'Learning Sessions'[Index]
            = CALCULATE (
                MAX ( 'Learning Sessions'[Index] ),
                ALLEXCEPT (
                    'Learning Sessions',
                    'Learning Sessions'[Participant_ID],
                    'Learning Sessions'[Unique_Test_ID]
                )
            )
    )
)

Best regards,

Yuliana Gu

[theitguy]

 Hi Yuliana @v-yulgu-msft,

thank you very much!

The last row for [Score] is the max of [Single_Session_ID]. 😉

I have got further questions to your solutions.

• Can I filter the outcome of Measure2, for example only scores higher than 50 and those count those?
• Can I create the average of the outcome of measure 1 afterwards?

[v-yulgu-msft]

Hi @theitguy,

---

• Can I filter the outcome of Measure2, for example only scores higher than 50 and those count those?

---

Do you want the table visual only show records where Measure2 is greater than 50? If so, you could apply the visual level filter as below:

• Can I create the average of the outcome of measure 1 afterwards?

---

Do you want to get the average outcome per Participant_ID per Unique_Test_ID? If so, please modify the formula for Measure1:

Measure1 =
DIVIDE (
    CALCULATE (
        SUM ( 'Learning Sessions'[Seconds_Spend] ),
        ALLEXCEPT (
            'Learning Sessions',
            'Learning Sessions'[Participant_ID],
            'Learning Sessions'[Unique_Test_ID]
        )
    ),
    CALCULATE (
        COUNTROWS ( 'Learning Sessions' ),
        ALLEXCEPT (
            'Learning Sessions',
            'Learning Sessions'[Participant_ID],
            'Learning Sessions'[Unique_Test_ID]
        )
    )
)

Best regards,

Yuliana Gu

[theitguy]

Hi @v-yulgu-msft,

thank you very much! But I am sorry, I think you got me wrong.:

• Can I create the average of the outcome of measure 1 afterwards?

What I wanted to achieve was something like 

(197+974+439+1+78+201)/6. Summing up the aggregated tests sums and divive them bei the number of distinct test_IDs. So that I would actually get one number as an output afterwards

Same goes for 

• Can I filter the outcome of Measure2, for example only scores higher than 50 and those count those?

I would like to count only the testIDs where the score is higher than 50. Therefore,  I would expect to see "2" as a result, in a card visualization for example.

[Hardik]

Hi @theitguy, Just a query here are all the fields coming from same table ? 

[theitguy]

Hi @Hardik,

the fields from the Learning Sessions are from the same table, yes.

Orginally, I duplicated the Learning Sessions-Query (by referencing the original) and did the grouping in the new table which worked very well by using power query formulas. It was easy to group the sessions into tests and get the desired values.

But today I noticed, that my colleagues will find it hard to set filters in the future, because it is hard to identifiy whether they set filters for the table with the sessions or the aggregated table. Therefore, I am trying to do all the grouping stuff via measures.

EDIT: I think, the problem is that I have to aggregate the values I get again, so that I receive a scalar value. Even if I cannot determine value on a user-test basis then. I have tried the following, but even that does not work:

Seconds_Spend_Corrected_Grouped = VAR MYTempTable = SUMMARIZE(Learning_Sessions;Learning__Sessions["Unique_Test_ID];"Seconds";SUM(Learning__Sessions[Seconds_Spend])) RETURN AVERAGE(MyTempTable[Seconds])