Create a summary table using SUMMARIZE and FILTER formula with multiple columns

[adni85]

Hi 

I am creating a summary table to include the number of occurrences of a particular value just like COUNTIFS in excel. Below is the Data and Rating table from which I want this Result table. The DAX formula I have used can only create one 2 columns (Genre & Good), I want to know how to add 2 more columns. 

Result = 

SUMMARIZE (

          FILTER('Data', 'Data'[Value] = "g" || 'Data'[Value] = "e"),

          'Data' [Genre],

          "Good", COUNT('Data'[Value])

)

Data
Index	Genre	Value
1	Classic	g
2	Rock	g
3	Jazz	b
4	Rock	i
5	Rock	b
6	Action	e
7	Comedy	e
8	Jazz	g
9	Jazz	g
10	Jazz	g
11	Rock	g
12	Rock	g
13	Comedy	e
14	Comedy	i
15	Comedy	i
16	Comedy	f
17	Classic	g
18	Action	e
19	Action	f
20	Classic	b
21	Classic	i
22	Classic	b
23	Classic	b
24	Jazz	g
25	Rock	b
26	Action	i
27	Jazz	i
28	Jazz	i

Rating
		Good	Bad	Indifferent
Music	Classic	g	b	i
	Rock	g	b	i
	Jazz	g	b	i
Films	Action	e	f	i
	Comedy	e	f	i

Result
Genre	Good	Bad	Indifferent
Classic	2	3	1
Rock	3	2	1
Jazz	4	1	2
Action	2	1	1
Comedy	2	1	2

[adni85]

Yes, it worked!

Thanks to @goncalogeraldes 

[goncalogeraldes · Accepted solution]

Hello there @adni85 ! Check if this works for you:

Result =
SUMMARIZE (
    'Data',
    'Data'[Genre],
    "Good",
        CALCULATE (
            COUNT ( 'Data'[Value] ),
            FILTER ( 'Data', 'Data'[Value] = "g" || 'Data'[Value] = "e" )
        ),
    "Bad",
        CALCULATE (
            COUNT ( 'Data'[Value] ),
            FILTER ( 'Data', 'Data'[Value] = "b" || 'Data'[Value] = "f" )
        ),
    "Indifferent", CALCULATE ( COUNT ( 'Data'[Value] ), FILTER ( 'Data', 'Data'[Value] = "i" ) )
)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes