Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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
1[email protected]3
1[email protected]3
2[email protected]3
3[email protected]2
4[email protected]2
6[email protected]4
7[email protected]4
7[email protected]4
7[email protected]4

 

Thanks in advance for any replies

  • TickboxPhil's avatar
    TickboxPhil
    6 years ago

    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!

3 Replies

  • TickboxPhil's avatar
    TickboxPhil
    Icon for Advocate IV rankAdvocate IV
    6 years ago

    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!
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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.