Count distinct values in hierarchy

[maryJ]

Hi all,
I am new to Power BI. Hope somebody can help me with my question.

I have a hierarchy in my SQL view and I want to calculate my team size.
Here is an example:
My Hierarchy columns:
Reporter
   Reporter -1
         RM
            Employee


My Reporter should see his team size (distinct count of  Reporter -1, RM and Employee). NOTE: Sometimes Reporter -1 can be RM.

Reporter -1       
A
B
C
D
E

RM
B
D
F
G
K

Employee
L
M
N
O


I thought it would be simple and tried to calculate like this: 
Measure = DISTINCTCOUNT(Table1[Reporter -1]) + DISTINCTCOUNT(Table1[RM]) + DISTINCTCOUNT(Table1[Employee])
However, it counts the same values twice (in Reporter-1 & RM columns)
Should be 12 , but it calculates 14 ( B & D calculated twice)

Hope you understood my case!

Thank you in advance!

[PattemManohar]

@maryJ Please try this as "New Measure" (I've added all three fields in same table as test data but the logic will be almost same even if the fields are in separate tables). The test data looks like this..

Test34 = 
VAR _Reporter = DISTINCTCOUNT(Test34HierarchyCount[Reporter])
VAR _DistinctReporter = DISTINCT(Test34HierarchyCount[Reporter])
VAR _RM = CALCULATE(COUNTROWS(Test34HierarchyCount),NOT(Test34HierarchyCount[RM] IN _DistinctReporter))
VAR _Employee = COUNTROWS(FILTER(Test34HierarchyCount,Test34HierarchyCount[Employee]<>BLANK()))
RETURN _Reporter + _RM + _Employee

[maryJ]

@PattemManohar, thank you very much for your quick help 
It works perfectly for my test data, but for SQL view table it still multiplies values.
Probably, because I use one SQL source table and there is a date column (each employee reports time for each day) and Project column (one employee might have multiple projects) + hierarchy ...
In database I have about 3000 rows and in SQL with union operator it returns correct number of people.
When I used your formula in Power BI it shows me about 4500 people, which is not correct (should be 120)
I modified a little your measure:

Test = 
Var Reporter = CALCULATE(COUNTROWS(DISTINCT('FACT_RM_REPORT_WIDE'[REPORTER])),ALL('FACT_RM_REPORT_WIDE'[CALENDAR_DATE]))
Var DistReporter = DISTINCT('FACT_RM_REPORT'[REPORTER])
Var RM = CALCULATE(COUNTROWS(DISTINCT('FACT_RM_REPORT_WIDE'[RM])),NOT('FACT_RM_REPORT_WIDE'[RM] IN DistReporter))
Var Employee = CALCULATE(COUNTROWS(DISTINCT('FACT_RM_REPORT_WIDE'[EMPLOYEE])),FILTER('FACT_RM_REPORT_WIDE','RM_REPORT_WIDE'[EMPLOYEE]<>BLANK()), ALL('FACT_RM_REPORT_WIDE'[CALENDAR_DATE]))
Return Reporter +RM+Employee

It looks better now (137 people) , but with 17 duplicate values...

Not sure how to exclude these duplicates in hierarchy. Hope you can advise what should be changed/added in DAX 

I appreciate your help!

[PattemManohar · Accepted solution]

@maryJ Did you tried debugging by returning each count separately (Reporter,RM,Emloyee) so that you can easily figure it out where the duplicate counts are coming from. 

Also, as per test data provided - I didn't check whether Reporter or RM is present in Employee (to exclude counting them again) but if that is not the case in your actual data then you need add those filter criteria to Employee Count as well.

[maryJ]

@PattemManohar
ohhh, you was absolutely right! I added check in my Dax in order to verify if RM is present in Employee column and it showed me a correct number . Finally!!   The problem was in hierarchy levels, so it appeared that RM also could be an employee...
Problem solved! 
Thank you very much! 
I really appreciate your quick help! 
Have a great day!

[PattemManohar]

@maryJ That's cool