Forum Discussion
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 com...
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:
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:
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!