COUNT OVER PARTITION in DAX

[Anonymous]

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

SalesOrderID	EMAIL	EmailCount
1	a@hotmail.com	3
1	a@hotmail.com	3
2	a@hotmail.com	3
3	b@gmail.com	2
4	b@gmail.com	2
6	c@yahoo.com	4
7	c@yahoo.com	4
7	c@yahoo.com	4
7	c@yahoo.com	4

Thanks in advance for any replies

[TickboxPhil · Accepted solution]

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])
        )

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]

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...

[Anonymous]

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.