topic Create a summary table using SUMMARIZE and FILTER formula with multiple columns in DAX Commands and Tips

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

adni85 — Fri, 18 Mar 2022 14:55:09 GMT

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

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

goncalogeraldes — Fri, 18 Mar 2022 15:02:37 GMT

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

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

adni85 — Fri, 18 Mar 2022 16:19:43 GMT

Yes, it worked!

Thanks to goncalogeraldes