Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!
Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors