Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
apmulhearn
Helper III
Helper III

Distinct Count of Data Based on Contact ID Relationship and Date Criteria - 2 tables

Hello,

 

I have  Table1 with Email Event Type Activity, Dates and Contact IDs.

I have Table 2 with ContactID and Reservation Date.

I want to know how many DIFFERENT Email Names a contact CLICKED (Action) BEFORE Their Book Date in Table2.

Thank you for any help.

 

 

apmulhearn_1-1668730829797.png

ContactIDEmail DateEmail NameActionBookDateContactIDResultDistinct Count of Email Name Clicks BEFORE Book Date in Table 2

Table 1    Table 2  Desired Measure Results   
     
1234561/1/2020ZebraClick 6/1/2021123456 1234563   
1234565/1/2020ZebraOpen 7/1/2021789654 7896541   
1234561/1/2021CheetahOpen         
1234561/15/2021ElephantOpen         
1234562/1/2021ZebraClick         
1234562/6/2021PantherClick         
1234562/9/2021PantherClick         
1234563/1/2021CheetahClick         
1234563/5/2021ZebraClick         
1234561/1/2022PantherClick         
1234568/1/2022ZebraClick         
1234569/1/2022CheetahClick         
7896542/1/2020LionClick         
7896542/1/2020LionOpen         
7896542/2/2021LionClick         
7896542/2/2022ZebraClick         
             
             
1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @apmulhearn ;

You could check the relationship about two tables:

vyalanwumsft_0-1669014395165.png

Then create a measure.

count = 
CALCULATE(DISTINCTCOUNT('Table1'[Name]),FILTER('Table1',[Action]="Click"&&[Date]>=MAX('Table2'[BookDate])))

The final show:

vyalanwumsft_1-1669014500584.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @apmulhearn ;

You could check the relationship about two tables:

vyalanwumsft_0-1669014395165.png

Then create a measure.

count = 
CALCULATE(DISTINCTCOUNT('Table1'[Name]),FILTER('Table1',[Action]="Click"&&[Date]>=MAX('Table2'[BookDate])))

The final show:

vyalanwumsft_1-1669014500584.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@apmulhearn , You can join them in Power bi and then can get

 

distinctcount(Table1[Email]) and plot with contact id or date from table 2

 

or you https://docs.microsoft.com/en-us/dax/treatas-function

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.