I have a universe issue where I have table a) tb.client which holds a unique reference for each client. I have this unique ID joined to another Table (table b) which has either no corresponding record, a single record or multiple records for each unique ID. Within the uiverse I have a measure called client count that is a count of this unique ID from table a. When I insert an object from table b) my count of unique ID becomes a count of everyline in the query result. The tables are joined with a one to many relationship and a double outer join. I am at an absolute loss as to what is causing the result. I have tried count distinct in the universe and it has had no effect. Please if you can suggest any possible causes I would be most grateful.
I don’t think this is a Fan Trap, the reason being I have a one to many relationship from Table a) but table b) has a Many to One Relationship, as far as I am aware a Fan trap only occurs when you have a one to many followed by a one to many relationship.
OK If I return from table a) 8 clients. and table b) contains 1 record for 7 of those clients and 6 records for 1 of the clients the count of client returns 13 not 8. My measure object is count(table a Client) not count (table b client)
I have tried that and it made absolutely no difference!
i.e. Count(DISTINCT Table a Client)
The object in the universe is set as a sum so would that have made my DISTINCT not work? Apologies but I have very limited SQL and Designer background!
I still do thing that it’s a fan trap - the number of records from table A are duplicated as many times as many corresponding records are found in the table B - and that’s all because of the 1:N nature of the join between A and B tables. It does not matter that there is no dimension/lookup table joined to the table A as many fan trap descriptions mention.
If the COUNT(DISTINCT tableA.client) did not help then I suggest to solve the problem as any other fan trap problem - using alias of the table A from where the measure COUNT(tableA.client) will be “sourced”.
(BOB member since 2010-04-16)
(BOB member since 2003-11-27)
(BOB member since 2002-06-20)