cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

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

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
1 ACCEPTED SOLUTION
Super User

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" )
),
CALCULATE (
COUNT ( 'Data'[Value] ),
FILTER ( 'Data', 'Data'[Value] = "b" || 'Data'[Value] = "f" )
),
"Indifferent", CALCULATE ( COUNT ( 'Data'[Value] ), FILTER ( 'Data', 'Data'[Value] = "i" ) )
)``````

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

2 REPLIES 2
Frequent Visitor

Yes, it worked!

Thanks to @goncalogeraldes

Super User

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" )
),
CALCULATE (
COUNT ( 'Data'[Value] ),
FILTER ( 'Data', 'Data'[Value] = "b" || 'Data'[Value] = "f" )
),
"Indifferent", CALCULATE ( COUNT ( 'Data'[Value] ), FILTER ( 'Data', 'Data'[Value] = "i" ) )
)``````

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors