Distinct Count of sub table


We have two custom tables that are similar to the Sales Header/Sales Line. I would like a distinct count of a 'codes' field on the line table (kind of like a product group code) for each record linked to the header table.

I am trying a couple of different approaches, the DistinctCount in the Measure is working but the joint to the header is giving me headaches so I end up with a distinct count of codes in all line records regardless of the header record involved.

Also, I was thinking about creating a custom lookup field in the header table (In the staging database) with a calculation of distinct count by the code in the linked lines table.

Any one encounter this before and/or have suggestions on how to fix?




Please sign in to leave a comment.