DAX with Count

[ani_informa]

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, adam@msn.com comes three times and matt@ol.com comes twice which is more than 1..so In following example, I want to show 5 on card ( 3 times adam + 2 times matt ). combination of PersonId and Email is always going to be unique.

[v-juanli-msft · Accepted solution]

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.

[Tahreem24]

Hi @ani_informa ,

For your requirement you just need to create one measure which count the email id.

Count = COUNT(Sheet1[Email])

Then take one slicer for email id and Card to show the above DAX formula.

Find the below screen shot for your reference.

Please give Kudos to this Efforts and accept this as a solution if it helps!

[ani_informa]

Hi Tahreen

I do not want to show value as per email filter.

[Gordonlilj]

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]

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