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

[ymc]

My database has records as shown below. 

Location	Event	Owner	Hour
X	E1	A	2
X	E2	B	3
X	E3	C	4
Y	E2	D	5
Y	E3	E	6
Y	E4	F	7
Z	E1	G	8
Z	E3	H	9
Z	E5	I	10

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

Location	Event	Owner	Hour	Event	Owner	Hour
X	E1	A	2	E3	C	4
Z	E1	G	8	E3	H	9

How to do that in Power BI ? 

[ymc]

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]

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]

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. 

[v-jianboli-msft]

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.