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
peekayza
Frequent Visitor

Checking conversions using patient names

Hi everyone

 

This seems so basic but it's getting the best of me. I'm trying to track conversions on all our marketing contact forms against our internal new patient records. The only field to compare unfortunately is name (which I realise will have a margin of error). I can't seem to link these values relationship wise so I was thinking a lookup could potentially work. Any assistance would be greatly appreciated. Thanks.

 

Screenshot 2022-01-11 122506.png

5 REPLIES 5
DataZoe
Employee
Employee

Hi @peekayza ,

 

In this situation I would create a table that has all the Name values from the other tables (which would be your lookup table or dimension table), de-duplicated. Then you can join that to the other tables (New Patients, SS Contact Form, GDC Contact Form).  

Modeling -> New Table:

Names =
DISTINCT (
UNION (
UNION ( DISTINCT ( Fact1[first_name] ), DISTINCT ( Fact2[first_name] ) ),
DISTINCT ( Fact3[first_name] )
)
)
 
(You can also create the Names table in Power Query too, which is a better option if you have millions of rows).
 
Relationships:
DataZoe_0-1641915670894.png

 

Make sure it's 1:Many from Names to the other tables.

 

Measures:

 

In Fact 1 = COUNTROWS(Fact1)
In Fact 2 = COUNTROWS(Fact2)
In Fact 3 = COUNTROWS(Fact3)
 
In Fact 1 and Fact 2 % of Fact 1 =
DIVIDE (
SUMX ( Names, IF ( [In Fact 1] = 1 && [In Fact 2] = 1, 1, BLANK () ) ),
[In Fact 1]
)
 
(you could even change this to >=1 if you have multiple matches by name)
 
Output:
DataZoe_1-1641915805742.png

 

Here I can see of the 25 people in Fact 1, only 1 is in Fact 2, or 4% of Fact 1.

 

I've attached a PBIX with this set up for you to look at.

 

Hope this helps!

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

Thank you so much! I will look at this right away and report back soonest. Does it make a difference that my name field is both first and last name combined?

@peekayza No, it doesn't make a difference. You want to try and make it as identifying as possible so it's best to keep the first name and last name together.

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

Hi @DataZoe 

 

Just checking in if you'd seen my latest update here. Many thanks.

Hi Zoe

 

Thanks again for your help and patience. So I think I took it a slightly different approach to you. I am wanting to check conversions per brand (TDIC, GDC and SS) against the new patients table names. 

 

So as first step I've managed to merge all the GDC names into a single conversions table with names. What I am struggling with is the next step of comparing it against the new patient names. I just can't see any easy way to bring the name in from that table. I tried this step below. Any assistance would be appreciated thanks. 

 

Screenshot 2022-01-20 174502.png

Screenshot 2022-01-20 173536.png

 

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

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.