Power Pivot / DAX - Distinct Count of one dim column from multiple fact tables

Question

Using Power Pivot in Excel 2016

I have one dim table called "Roster" with 218 unique 'Employee Names' and other attributes for the employees

I have three fact tables called "Forecast," "Actual," and "Invoice," each with the related 'Employee Name' columns, in addition to many other attributes and values. I want a distinct count of 'Employee Name' across all three of those tables, depending on what I'd like to pivot them by in my pivot table, like 'Project' or 'Company.' I've read about counting across multiple columns from one table, but I'm trying to count across multiple tables.

When I create measure of: Headcount:=calculate(DISTINCTCOUNT('Roster_Table'[Employee Name]),'Actual','Forecast','Invoice'), and throw it in my pivot table, I get a very small count of 14, which is probably the ones that are unique to only one of the three tables.

When I create measure of: Headcount:=DISTINCTCOUNT('Roster_Table'[Employee Name]), I get all 218

The true number should be around 170. Any ideas of how to make this work?

Thanks

Can you provide a minimal working example, This is pretty difficult to answer in the abstract without knowing what your tables look like. — Alexis Olson
Yes, I'll try to make some dummy data later today and update my question. It basically just needs to look at three columns from three tables and count the unique names from the combination of all three. — Brian

GregGalloway GregGalloway · Accepted Answer · 2018-02-08T15:05:01

Please try the following DAX calculation:

COUNTROWS(
 DISTINCT(
  UNION(
   VALUES('Forecast'[Employee Name]),
   VALUES('Actual'[Employee Name]),
   VALUES('Invoice'[Employee Name])
  )
 )
)

Power Pivot / DAX - Distinct Count of one dim column from multiple fact tables

1 Answers