06-21-2017 03:22 AM
Normally i'd try to puzzle these issues out myself but this one has me completely stumped.
Table A - a record of all journeys taken by pupils from homje to school. 14.2k rows total 11k distinct
Table B - lots of data on schools, postcode, address,area,contact name etc 542 distinct rows
I have a table that contains all of the journeys made by children in our area to thier school (Table A). This table contains only the name of the school. The column in the table that contains the data is called just called school
I then have a second table (Table B) that contains lots of information about the schools like postcode, school type, contacts details etc . The issue I have is that when I create the relationship I get lots of phantom blank values. I think because there is no corresponding value from A to B. My problem Is compounded by the fact the the schools in table A have slight differences in spelling and spacing to the reference table.
I tried merging the tables and can get 6.5k out of 14000 matches.
I cant post the tables becuase the info is sensitive but id really appreciate any suggestions. I've put the model below though. I was wondering if there is a DAX formula to match partial values? between A and B
the aim is to map the flows
Many thanks for any advice you can offer
06-21-2017 03:54 AM
I don't know to what extend the slight differences in spelling and spacing you have in your model however you can create a ID column in both tables that removes the spacing and gets part of the names (first 3 letters and last 3 letters or something similar) or make everything in CAPS and then use that columns as a basis for your relationship between tables.
Without data it's difficult to give you an example of the changes that can be made to your information.
06-28-2017 01:56 AM
further to this i've anonymised the information as much as I can so i'll post some shots hopefully it'll give a better picture of what im trying to do.
I have two tables - table a contains finance information and table b contains journey information. my problem is that when i count the data there are more distinct entries on the journey sheet than there are on the finance sheet. shown in the picture below. the aim is to identify the ones on the journey sheet that dont appear on the finance sheet in order to extract them and send them over to the orgintor. ive been trying to mess around with dax but i dont understand it enough yet to make it work properly - any help is appreciated
I've extracted the route column from each of the master tables as new queries - the intention is to make a route key. they are set up as follows
the small one is the finance routes and the large one 14k rows is the journey sheet