Countax / Earlier -- Measure to count subtotals

[charleshale]

I have been beating my head against the wall all day trying EARLIER and COUNTAX but failing to see what I am doing wrong.  It must be obvious.  Any advice?

I am analyzing a simple 8 million row flat file showing email sub and the email list they subscribe to.    Although I'm actually using hashcodes, the data is essentially the following:

COL A   - SUBSCRIBER                                       COL B  - NEWSLETTER                            

<joe.smith@gmail.com>                                  <"Daily Cat News - Meow!">

<joe.smith@gmail.com>                                  <"Daily Dog News - Ruff!">

<joe.smith@gmail.com>                                  <"Daily Rabbit News">

<I-love-snakes@gmail.com>                           <"Daily Snake News - Hiss!">

Of course, I can easily get the distinct count of overall subs across my newsletters (which is in this example is two unique subs) but I want something different: a measure that shows the total sum of distinct email subs by unique item in column B.   In other words, I'd like to show

DISTINCT COUNT BY NEWSLETTER                                           DISTINCT SUBS TO NEWSLETTER                                

"Daily Cat News - Meow!"                                                                          1                   

"Daily Dog News - Ruff!"                                                                            1         

"Daily Rabbit News"                                                                                    1         

"Daily Snake News - Hiss!"                                                                          1   

TOTAL SUM OF DISTINCT SUBS AT NEWSLETTER LEVEL                             4

Of course, this is is super easy in excel using arrays or pivot tables, or even using OLAP to connect to excel, but, at this point, I am TOTALLY obsessed with using DAX to make a measure to do it.  I'm a geek- but feeling like a woefully dumb geek.  The closest I have gotten is:   

Result = CALCULATE(COUNTAX('Table1',Table1'[Email]),FILTER('Table1',[Email]=EARLIER('Table1'[Email]))).

I am thinking I use CountAX to deal with the text, and make it a result since EARLIER works with calculated columns.   The error message I get is the dreaded "EARLIER/EARLIEST refers to an earlier row context which doesn't exist".

Any advice?

Thank you.

Charlie

[charleshale]

I have been beating my head against the wall all day trying EARLIER and COUNTAX but failing to see what I am doing wrong.  It must be obvious.  Any advice?

I am analyzing a simple 8 million row flat file showing email sub and the email list they subscribe to.    Although I'm actually using hashcodes, the data is essentially the following:

COL A   - SUBSCRIBER                                       COL B  - NEWSLETTER                            

<joe.smith@gmail.com>                                  <"Daily Cat News - Meow!">

<joe.smith@gmail.com>                                  <"Daily Dog News - Ruff!">

<joe.smith@gmail.com>                                  <"Daily Rabbit News">

<I-love-snakes@gmail.com>                           <"Daily Snake News - Hiss!">

Of course, I can easily get the distinct count of overall subs across my newsletters (which is in this example is two unique subs) but I want something different: a measure that shows the total sum of distinct email subs by unique item in column B.   In other words, I'd like to show

DISTINCT COUNT BY NEWSLETTER                                           DISTINCT SUBS TO NEWSLETTER                                

"Daily Cat News - Meow!"                                                                          1                   

"Daily Dog News - Ruff!"                                                                            1         

"Daily Rabbit News"                                                                                    1         

"Daily Snake News - Hiss!"                                                                          1   

TOTAL SUM OF DISTINCT SUBS AT NEWSLETTER LEVEL                             4

Of course, this is is super easy in excel using arrays or pivot tables, or even using OLAP to connect to excel, but, at this point, I am TOTALLY obsessed with using DAX to make a measure to do it.  I'm a geek- but feeling like a woefully dumb geek.  The closest I have gotten is:   

Result = CALCULATE(COUNTAX('Table1',Table1'[Email]),FILTER('Table1',[Email]=EARLIER('Table1'[Email]))).

I am thinking I use CountAX to deal with the text, and make it a result since EARLIER works with calculated columns.   The error message I get is the dreaded "EARLIER/EARLIEST refers to an earlier row context which doesn't exist".

Any advice?

Thank you.

Charlie

[ChrisMendoza · Accepted solution]

hi @charleshale, EDIT comment at bottom...

Presuming you have the option to use the Query Editor...

Then a simple SUM( ) measure could yield this result:

If you need a DAX solution, I'm sure there's a way. I just immediately went to the M Code for some reason.

I realize I have to make assumptions about how you want to use your measures, maybe I'm over simplifying as well, but if the desired output is a table or maybe a chart the DAX could be as simple as:

Sum Distinct Subs At Newsletter Level(DAX) = COUNTA('Table2 (2)'[subscriber])

The visuals already filter 'newsletter' by unique and produce the below for me.

Maybe we need more information about how you would like to use the measure you're trying to create?

[charleshale]

Thank you, @ChrisMendoza.

Interim update: I've been worried about processor load trying the group query editor function but am trying it now on a 24 core workstation.  It's chunking away.   Will follow up to channel as soon as it finishes with results.   (NB: in example below, Scrubbed WBS Base = Newsletter name).  Thank you again. Charlie