Forum Discussion

ymc's avatar
ymc
New Member
3 years ago

How to create a summary table or matrix for different value combination from the same column

My database has records as shown below. 

 

LocationEventOwnerHour
XE1A2
XE2B3
XE3C4
YE2D5
YE3E6
YE4F7
ZE1G8
ZE3H9
ZE5I10

 

I need to find out which location has both event E1 and E3, and need to generate a summary table as shown below. 

 

LocationEventOwnerHourEventOwnerHour
XE1A2E3C4
ZE1G8E3H9

 

How to do that in Power BI ? 

Need Help
Tutorial Requests

4 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    Hi ymc ,

     

    Please try:

    summary table =
VAR _a =
    FILTER ( 'Table', 'Table'[Event] IN { "E1", "E3" } )
VAR _b =
    FILTER (
        _a,
        COUNTROWS ( FILTER ( _a, [Location] = EARLIER ( 'Table'[Location] ) ) ) = 2
    )
RETURN
    SUMMARIZE (
        _b,
        'Table'[Location],
        "Event_1", "E1",
        "Owner_1", CALCULATE ( MAX ( 'Table'[Owner] ), FILTER ( 'Table', [Event] = "E1" ) ),
        "Hour_1", CALCULATE ( SUM ( 'Table'[Hour] ), FILTER ( 'Table', [Event] = "E1" ) ),
        "Event_3", "E3",
        "Owner_3", CALCULATE ( MAX ( 'Table'[Owner] ), FILTER ( 'Table', [Event] = "E3" ) ),
        "Hour_3", CALCULATE ( SUM ( 'Table'[Hour] ), FILTER ( 'Table', [Event] = "E3" ) )
    )

    Final output:

    Best Regards,

    Jianbo Li

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

  • ymc's avatar
    ymc
    New Member
    3 years ago

    Thanks for the quick response and solution. Is there another way to generate such summary table by using visulal only? I am very new to PowerBI, not sure how to impletement those codes actually. 

    • v-jianboli-msft's avatar
      v-jianboli-msft
      Community Support
      3 years ago

      Hi ymc ,

       

      Please try:

      First create these measures:

      Event_1 = CALCULATE(MAX('Table'[Event]),FILTER('Table',[Event]="E1"))

Hour_1 = CALCULATE(SUM('Table'[Hour]),FILTER('Table',[Event]="E1")) 

Owner_1 = CALCULATE(MAX('Table'[Owner]),FILTER('Table',[Event]="E1"))

Event_3 = IF([Event_1]<>BLANK(),CALCULATE(MAX('Table'[Event]),FILTER('Table',[Event]="E3")))

Hour_3 = IF([Event_1]<>BLANK(),CALCULATE(SUM('Table'[Hour]),FILTER('Table',[Event]="E3")))

Owner_3 = IF([Event_1]<>BLANK(),CALCULATE(MAX('Table'[Owner]),FILTER('Table',[Event]="E3")))

      You can copy and paste them here:

      Then put them and 'Location' into the table visual:

      Final output:

      Best Regards,

      Jianbo Li

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

      • ymc's avatar
        ymc
        New Member
        3 years ago

        Hi, Thanks for the detailed instruction for the solution. It works as expected however pretty time consuming.

         

        Meanwhile after some searching, I found a trick by dupicating the data source table, then linking them together by location. Now I can quickly use visual filters to generate a table I need. 

         

        Thanks again for your help anyway.