Group By Won't Work - How to aggregate?

Question

Hi guys,&nbsp;What I have:Learning Sessions:Participant_IDUnique_Test_IDSingle_Session_IDLaunch_DateSeconds_SpendStatusScore1t_012098417-11-2017444incomplete01t_012327117-11-201724incomplete01t_012327217-11-2017123complete121t_022327319-11-2017974complete364t_032097023-11-2017439incomplete04t_042330123-11-20171complete665t_052093505-11-201784incomplete05t_062181905-11-201728incomplete05t_052181717-11-201772incomplete05t_062098317-11-2017373complete80&nbsp;Participants:Participant_IDNameDepartmentCodeHire_Date1John Doe1234501-11-20172Max Mustermann1234501-11-20173John Smith1234501-11-20174Jan Janssen1234501-11-20175John Blow12345601-11-2017&nbsp;Want I want:Measure1: For each test of a participant, I want the sum&nbsp;of [Seconds_Spend], e.g. for participant "1" and test with id "t_01" I want&nbsp;"591"Measure2:&nbsp;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&nbsp;multiple columns cannot be converted to a scalar value. Do I have to change my database design to achieve what I want?

hardik · Answer

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

theitguy · Answer

Hi&nbsp;Hardik,&nbsp;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.&nbsp;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.&nbsp;EDIT: I think,&nbsp;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])

v-yulgu-msft · Answer

Hi&nbsp;theitguy,
&nbsp;

Measure1: For each test of a participant, I want the sum&nbsp;of [Seconds_Spend], e.g. for participant "1" and test with id "t_01" I want&nbsp;"591"
Measure2:&nbsp;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"&nbsp;

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&nbsp;Participant_ID per&nbsp;Unique_Test_ID.
&nbsp;
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&nbsp;=CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;SUM&nbsp;(&nbsp;'Learning&nbsp;Sessions'[Score]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Learning&nbsp;Sessions',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Learning&nbsp;Sessions'[Index]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MAX&nbsp;(&nbsp;'Learning&nbsp;Sessions'[Index]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLEXCEPT&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Learning&nbsp;Sessions',&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Learning&nbsp;Sessions'[Participant_ID],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Learning&nbsp;Sessions'[Unique_Test_ID]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;))

&nbsp;
Best regards,
Yuliana Gu

theitguy · Answer

&nbsp;Hi Yuliana&nbsp;v-yulgu-msft,&nbsp;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 · Answer

Hi&nbsp;theitguy,
&nbsp;

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:

&nbsp;
&nbsp;

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

Do you want to get the average outcome per&nbsp;Participant_ID per&nbsp;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]
        )
    )
)

&nbsp;
Best regards,
Yuliana Gu

Forum Discussion

Group By Won't Work - How to aggregate?

6 Replies

Related content

Power Query - Aggregate multiple columns within group by

DirectQuery group by/aggregates

Fabric & Azure Data User Group Program Requirements & Benefits

Community User Group Experience

Data Aggregation issue

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

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