Forum Discussion

NullMN's avatar
NullMN
Frequent Visitor
3 years ago
Solved

Formula help please - Count each unique email & then total count of those appearing once.

Hi - I'm fairly new to PowerBI and was hoping someone might be able to help.  I've been hunting in the Community and I'm struggling to find a way to accomplish this.

 

My team produces events and our goal is to be able to report out how many new attendees came to our events in total and by year.

 

 

I have a dataset that contains email addresses and purchase date along with many other columns.   Example below.

 

Original 
EmailPurchase Date
[email protected]1/1/2022
[email protected]3/15/2022
[email protected]10/22/2022
[email protected]2/5/2022
[email protected]3/1/2021
[email protected]6/5/2021
[email protected]2/29/2021
[email protected]5/3/2022
[email protected]12/3/2021
[email protected]2/3/2022

 

I'm hoping to be able to obtain a total number of times each email appears in the original dataset and then count only those where the results are equal to 1 and pull in the last purchase date from the original dataset so I have something to filter against.  I'm assuming I need to build a new table, but I've tried a number of dax formulas on the community to build the table and haven't found the right solution.  I'm hoping someone can help.

 

Expected results  
EmailResultsLatest Purchase date
[email protected]310/22/2022
[email protected]22/5/2022
[email protected]16/5/2021
[email protected]12/29/2021
[email protected]15/3/2022
[email protected]22/3/2022

 

Final Results
Count of number of times Results column= 13
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi NullMN ,

    I have created a simple sample, please refer to it to see if it helps you.

    Create 2 measures.

    Measure =
CALCULATE (
    COUNT ( 'Table'[Email] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Email] = SELECTEDVALUE ( 'Table'[Email] ) )
)

    Measure2 =
CALCULATE (
    MAX ( 'Table'[Purchase Date] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Email] = SELECTEDVALUE ( 'Table'[Email] ) )
)

     

    If I have misunderstood your meaning, please provide more details with desired output and pbix file without  privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • NullMN's avatar
    NullMN
    Frequent Visitor
    3 years ago

    Sorry the last line should have stated the number of times (1) appears in the results column = 3

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi NullMN ,

    I have created a simple sample, please refer to it to see if it helps you.

    Create 2 measures.

    Measure =
CALCULATE (
    COUNT ( 'Table'[Email] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Email] = SELECTEDVALUE ( 'Table'[Email] ) )
)

    Measure2 =
CALCULATE (
    MAX ( 'Table'[Purchase Date] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Email] = SELECTEDVALUE ( 'Table'[Email] ) )
)

     

    If I have misunderstood your meaning, please provide more details with desired output and pbix file without  privacy information.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Satya_Bhamidipa's avatar
    Satya_Bhamidipa
    Icon for Helper I rankHelper I
    3 years ago

    Please check my solution 

     

    Count of Mail ID =
    COUNTX (
        FILTER ( 'Sheet1 (2)', EARLIER ('Sheet1 (2)'[Mail ID] ) = 'Sheet1 (2)'[Mail ID] ),
        'Sheet1 (2)'[Mail ID]
    )