Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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?
Proud to be a Super User!
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?
Proud to be a Super User!
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
194 | |
111 | |
105 | |
79 | |
71 |