Forum Discussion
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, [email protected] comes three times and [email protected] 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.
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
MaggieCommunity 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.
5 Replies
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 workThanks for your reply but I cannot use this because I am using DirectQuery Mode and not Import.
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!Hi Tahreen
I do not want to show value as per email filter.
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
MaggieCommunity 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.
