cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhakyil
Frequent Visitor

COUNT OVER PARTITION in DAX

Hi all,

 

I need a conversion from SQL to DAX. It seems so simple but I could not figure it out.

What I need is, simply put, the number of email addresses against every line in the given table, here FactSales. I would like to have it as a calculated column rather than a measure.

 

SELECT EMAIL,COUNT(*) OVER (PARTITION BY EMAIL) NumberOfEmailAddress
FROM FactSales
WHERE EMAIL IS NOT NULL

 

SalesOrderIDEMAILEmailCount
1a@hotmail.com3
1a@hotmail.com3
2a@hotmail.com3
3b@gmail.com2
4b@gmail.com2
6c@yahoo.com4
7c@yahoo.com4
7c@yahoo.com4
7c@yahoo.com4

 

Thanks in advance for any replies

1 ACCEPTED SOLUTION
TickboxPhil
Advocate IV
Advocate IV

Hi mhakyil, this is classic "simple yet not easy"... because like your SQL PARTITION, the window is needed for context, so add calculated column as:

 

 

DAX EmailCount = 
    CALCULATE(
        COUNT('FactSales'[EMAIL]),
              ALLEXCEPT('FactSales', 'FactSales'[EMAIL])
        )

 

 

Email Count DAX COUNT Proper.jpg
 
You'll find a good explanation at https://stackoverflow.com/questions/49322212/dax-equivalent-to-t-sql-avg-overpartition-by where I got the idea - was intrigued myself so worked it out from there! Other methods involve EARLIER, and many book examples use hard-coded values in their filters ( = 2020, = "Blue" etc) to illustrate more basic points, which is not really relevant with dynamic req like this.
 
Note I did have to add an index column as the Table viz doesn't like non-unique records surprisingly (I thought only the Matrix acted like Pivot), but the result is the same without it.
 
Also note the total needs to be switched off, or at least averaged. Or in DAX, use HASONEVALUE to BLANK it - another little trick.
 
I had the same frustrations coming from years of SQL (I see you responded to my old SQL question, hence spotted this post!) as the functional language DAX requires very different thinking. You'll only be fully satisfied with Russo & Ferrari's "The Definitive Guide to DAX" (and reading it twice as they themselves note!) but its not light, and @Phil_Seamark published "Beginning DAX with Power BI" which is better described as its subtitle "The SQL Pro's Guide to Better Business Intelligence" - very useful and quickly opens up complex table manipulations which you'll probably need.
 
NB: @Greg_Deckler is right to correct even though your post is clear, he's usually incredibly helpful with great answers, so don't feel bad, just put any SQL after the question, not before I guess!
 
Have a great DAX journey, should be worth it in the end for all the cross-filtering etc!

View solution in original post

3 REPLIES 3
TickboxPhil
Advocate IV
Advocate IV

Hi mhakyil, this is classic "simple yet not easy"... because like your SQL PARTITION, the window is needed for context, so add calculated column as:

 

 

DAX EmailCount = 
    CALCULATE(
        COUNT('FactSales'[EMAIL]),
              ALLEXCEPT('FactSales', 'FactSales'[EMAIL])
        )

 

 

Email Count DAX COUNT Proper.jpg
 
You'll find a good explanation at https://stackoverflow.com/questions/49322212/dax-equivalent-to-t-sql-avg-overpartition-by where I got the idea - was intrigued myself so worked it out from there! Other methods involve EARLIER, and many book examples use hard-coded values in their filters ( = 2020, = "Blue" etc) to illustrate more basic points, which is not really relevant with dynamic req like this.
 
Note I did have to add an index column as the Table viz doesn't like non-unique records surprisingly (I thought only the Matrix acted like Pivot), but the result is the same without it.
 
Also note the total needs to be switched off, or at least averaged. Or in DAX, use HASONEVALUE to BLANK it - another little trick.
 
I had the same frustrations coming from years of SQL (I see you responded to my old SQL question, hence spotted this post!) as the functional language DAX requires very different thinking. You'll only be fully satisfied with Russo & Ferrari's "The Definitive Guide to DAX" (and reading it twice as they themselves note!) but its not light, and @Phil_Seamark published "Beginning DAX with Power BI" which is better described as its subtitle "The SQL Pro's Guide to Better Business Intelligence" - very useful and quickly opens up complex table manipulations which you'll probably need.
 
NB: @Greg_Deckler is right to correct even though your post is clear, he's usually incredibly helpful with great answers, so don't feel bad, just put any SQL after the question, not before I guess!
 
Have a great DAX journey, should be worth it in the end for all the cross-filtering etc!

View solution in original post

Greg_Deckler
Super User IV
Super User IV

I don't read SQL very well, what are you trying to do? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Note, that article specifically calls out, don't just post SQL and ask for a translation to DAX/M...


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I thought the code would speak for it self. What I need is, simply put, the number of email addresses against every line in the given table, here FactSales. I would like to have it as a calculated column rather than a measure.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors