Forum Discussion
Helper VNormalizing a many-to-many
Community ChampionHello aashton
create a table containing the unique candidateID and its related fields into a new table .
you can do it in poewr query or create a calucalted table using dax as follow :
dim_candidates =
var datasource_1 =
allnoblank(app_tbl[candidate_id])
var datasoource_2 =
allnonblank(contr_tbl[candidate_id]
RETURN
distinct(
union(
datasource_1 ),
datasoource_2
)
)
then link this new table to both tables .
but then, it all depends on what data you to show .
let me know if this works for you..
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠
Helper VDAN Yes, that is the way I am doing it. But I thought I was missing something, because I am having a ton of trouble getting things to work. The bigger picture is a table of Leads (people), ID, Name, Phone, Etc. These Leads can have contacted us via one or many methods, for example, through our website, social media, referrals, etc. Each of these methods is it's own table. These Leads can then submit one or more applications and be offered one or more contracts. I am creating a report that seeks to see how valuable/effective these lead types are (referrals, social media, website, etc), and need to display total number of leads each, application and contract counts by each. It's just been nothing but trouble getting all of this to display.
For example, when trying to display in a table, the Lead ID, Name, phone, Application ID and Date, Contract ID and date. I get errors saying it doesn't know the relationship between contracts.