Aggregation after simple many to one join, not working ?

[jono]

Hi, I have done a very simple many to one join between two small tables fill with only test data (both directions).  The join seems to work fine but when I make a graph and chose count or sum or any other aggregation the total comes out wrong.  It seems not to be counting any duplicate rows for example if I click on Count, or Count distinct the answers will come out the same which is not correct as there are duplicates in the column.  Attached is a picture of a graph with counts and then a table which shows the entire joined data table.  To me the graph is not giving the right output as I have definitely selected Count and not Count distinct

[jono]

The Tables look like pictures attached and the relationship is set up as attached as well.  Its very simple not much data, nothing to it but I cant work out why its not working

[jono]

As another piece of information when I merge the queries, (instead of just creating a relationship) it works perfectly.  I would have thought these two things would work the same way.  

Lastly when doing the relationship if I put a variable from the facttable on the x axis (and one from dimension table on the y axis) of the bar graph it always gives the wrong answer, but if I put a variable from the dimensiontable on the x axis (and a variable from the fact table on y axis) then it works just fine. I thought that the relationship would work the same in both directions??? 

[v-yuezhe-msft]

@jono,

When you only drag the CardStatus ID of dimension_table and CardNumber of facttable to create the graph, the table is based on the unique combination of CardStatus ID+ CardNumber which doesn’t contain duplicated CardStatus ID for each CardNumber, thus you don’t get correct count number in the graph.

In your scenario, please create the graph by using CardStatus ID and CardNumber of facttable.

Regards,
Lydia