Forum Discussion

ani_informa's avatar
ani_informa
Helper III
6 years ago
Solved

DAX with Count

Hi   I have a table with data like following snap. I want to write a DAX so that I can count the number of Emails. If Email count is more than 1 then sum those numbers. For example, in below snap, ...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi ani_informa 

    As tested under direct query connection, you could create measures instead of calculated columns.

    Measure = CALCULATE(COUNT('test1$'[PersonId]),ALLEXCEPT('test1$','test1$'[Email]))

Measure 2 = COUNTX(FILTER('test1$',[Measure]>1),[Measure])

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Gordonlilj's avatar
Gordonlilj
Solution Sage
6 years ago

Hi,

 

One way you could try and achieve this would be to first create a calculated column using something like below:

 

E-mail occurence  =
COUNTX ( FILTER (
 'Table', EARLIER ( 'Table'[Email] ) = 'Table'[Email] ),
    'Table'[Email]
)
 
Then creating a measure:
 
Count occurence > 1 =
CALCULATE( COUNT( 'Table'[Email] ),
                           FILTER( 'Table','Table'[Occurence] > 1 )
)
 
Perhaps not the most elegant solution but if i've understood you correctly than it should work
 
 

 

  • ani_informa's avatar
    ani_informa
    Helper III
    6 years ago

    Thanks for your reply but I cannot use this because I am using DirectQuery Mode and not Import.