Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count Measure based on all filters or columns except one

So I've got a table looking something like this that lists communications that have been sent and via what method

 

Customer ID MessageDelivery Method Sent Time
1Welcome EmailAM
1WelcomeLetterAM
2WelcomeEmailPM
3CongratsLetterAM
3CongratsEmailPM
3CongratsLetterAM

 

What I want is to put it into a table like this in a Power BI report

 

Customer ID MessageDelivery Method Count Sent Count Sent any Delivery Method 
1Welcome Email12
1WelcomeLetter12
2WelcomeEmail11
3CongratsLetter23
3CongratsEmail13

 

Now the first 4 columns are easy, but what I need is a measure for the last column. I need it as a measure as I would like it to work with filters. For example if I filtered to just PM, then the value should update.

 

The difficulty I'm having is that it's easy to count all rows. I can even count all rows that match just the customer ID and Message, but what I can't do is count all rows that match everything (those two columns plus any applied filters) regardless of the delivery method column.

 

Any help would be appreciated, thanks.

 

Edit: can't get the table to display correectly. It looks correct when I edit the post and if I try and change the column width I get an error saying invalid html

  • You can create a measure like

    Count sent any delivery method =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Delivery method] )
    )

1 Reply

  • You can create a measure like

    Count sent any delivery method =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Delivery method] )
    )