1 Reply Latest reply on Jan 17, 2020 6:51 AM by Robert Hyatt

    One to many join duplicates rows, blending nullifies filtering, solution?

    Robert Hyatt

      I found a forum thread on this exact subject from a few years back, but sadly it was never solved (probably not a good sign for me!).

       

      To keep it simple, I need to do a one-to-many join.

       

      When I join the tables in the data source tool, the rows are duplicated.  When I blend the data to get around this, I'm now unable to use any action filters on the blended data source.  So both options are failing for me.

       

      Is there no way around this? 

        • 1. Re: One to many join duplicates rows, blending nullifies filtering, solution?
          Robert Hyatt

          Through more searching I finally found the answer!  Thank you to Wim Kegels for the info he gave in a thread a few years back.

           

          Basically the workaround is to do the join, let the dups remain, then create an LOD calculated field with a MIN function to only calculate 1 of the dup rows in your totals.  I get this may not work for every situation, but it solved mine.  Below are the steps Wim gave.

           

          use Level of Detail calculations.  In a fully joined data source (30 records), the following resulted in the correct number of hours / employee:

          sum({ FIXED [Date], [Client]: MIN([Hours])}).  This calculation looks per date and per client, then selects the minimum amount of hours (which is the same for all three entries for that client on that date) and takes the sum over the whole dataset.