cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adni85
Regular 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
Regular 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
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors