Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
adni85
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  
IndexGenreValue
1Classicg
2Rockg
3Jazzb
4Rocki
5Rockb
6Actione
7Comedye
8Jazzg
9Jazzg
10Jazzg
11Rockg
12Rockg
13Comedye
14Comedyi
15Comedyi
16Comedyf
17Classicg
18Actione
19Actionf
20Classicb
21Classici
22Classicb
23Classicb
24Jazzg
25Rockb
26Actioni
27Jazzi
28Jazzi

 

Rating    
  GoodBadIndifferent
MusicClassicgbi
 Rockgbi
 Jazzgbi
FilmsActionefi
 Comedyefi

 

 

Result   
GenreGoodBadIndifferent
Classic231
Rock321
Jazz412
Action211
Comedy212
1 ACCEPTED SOLUTION
goncalogeraldes
Super User
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" )
        ),
    "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

View solution in original post

2 REPLIES 2
adni85
Frequent Visitor

Yes, it worked!

 

Thanks to @goncalogeraldes 

goncalogeraldes
Super User
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" )
        ),
    "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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors