Best approach for querying multiple tables
I'm not sure why you join your dependent tables with the distinctValuesView. Is it to rule out sourceValues that does not exist in Source? In that case you may try to use EXISTS instead:

SELECT q.SourceValue, SUM(q.Count) as Count
FROM (
    SELECT dt1.SourceValue, COUNT(*) AS Count
    FROM DependantTable1 dt1
    WHERE EXISTS (
        SELECT 1 FROM Source s
        WHERE s.SourceValue = dt1.SourceValueColumn
    )
    GROUP BY dt1.SourceValue

    UNION ALL

    ...
) q
GROUP BY q.SourceValue


Note that I changed UNION to UNION ALL. I assume that if you have 4 occurrences of 'value1' in DependantTable1 and 4 occurrences in DependantTable2 the sum should be 8. If you use UNION the result will be 4

If most of your SourceValue exists in Source, you may try to apply the union first:

SELECT q.SourceValue, COUNT(*)
FROM (
    SELECT dt1.SourceValue
    FROM DependantTable1 dt1
    UNION ALL
    SELECT dt2.SourceValue
    FROM DependantTable1 dt2
) q
WHERE EXISTS (
    SELECT 1 FROM Source s
    WHERE s.SourceValue = q.SourceValueColumn
) 
GROUP BY q.SourceValue