Perplexing issue with Counts in a table

[KarlE]

Hi all.  I have a table in Power BI (employee table).  This table obviously contains data such as employee number, name, email, etc.  It also has hire and termination date.  The Hire_Date and Termination_Date columns have a relationship with my date table.  Hire_Date is currently the active relationship.  This employee table has 139 rows (as seen in the lower left hand corner of the screen when in Table View mode.  I don't know if it is relevant or not, but this employee table has a one-to-many connection from it to to a time tracking table (single direction filtering).  This time tracking table has a many-to-many to a project table (both direction filtering).

My issue is that I have been getting odd results when creating measures using the employee table - essentially results that are larger than expected (verified by bringing in my data into excel and manually applying filters).  As part of my troubleshooting, I went with some simple count measures and am getting inconsistent results.  I am particularly perplexed by how one of my formulas returns a number that is 1 greater (140) than the total rows in my table.  Below are the DAX measures that I am using and their associated results:
The first measure returns what I am expecting (139 - the total number of rows in my table).

EmployeeCount_Debug (Troubleshooting) =

CALCULATE(

    COUNTROWS('Employee'),

    REMOVEFILTERS('Employee')

)

The second measure returns 140; one greater than than both the first measure and the total rows in my table.

EmployeeRowCountIgnoringFilters (Troubleshooting) = COUNTROWS(ALL('Employee'))

Any help in explaining why this is happening is greatly appreciated.  Thanks in advance.

[Shravan133 · Accepted solution]

• Inactive Relationships or Unrelated Rows: Sometimes, ALL can expose rows that are not normally visible due to relationships or filtering in the model. Check if there are any inactive relationships or rows in the employee table that might not have proper connections to related tables.

• Duplicate Rows: Even though your employee table shows 139 rows, there might be a hidden duplicate or some data integrity issue causing ALL to pick an extra row.

• Row Context: When using ALL, it removes all filters from the employee table, including those applied by related tables. This could result in a scenario where a row appears more than once due to the context of relationships.

Troubleshooting Steps

1. Verify Data Integrity: Check your employee table for any hidden or duplicate rows that might not be visible in Power BI but exist in the underlying data source.

2. Inspect Relationships: Ensure that all relationships between your tables are correctly set up and there are no inactive or ambiguous relationships causing unexpected results.

3. Check for Unrelated Rows: Create a measure to identify if there are any rows in the employee table that do not have corresponding related data:

   UnrelatedEmployeeCount = CALCULATE( COUNTROWS('Employee'), ISBLANK(RELATED('TimeTrackingTable'[SomeColumn])) )

4. Row-Level Filters: Test if there are any row-level filters affecting your count by creating a table visual and manually inspecting the rows.

Adjusted Measure (Alternative Approach)

If you want to ensure you count only the rows visible in the current filter context without exposing potential unrelated or hidden rows, you can use a measure like:

EmployeeRowCount = CALCULATE( COUNTROWS('Employee'), REMOVEFILTERS(ALL('Employee')) )

This measure would give you the count of rows after removing all filters, but it shouldn't expose hidden rows due to ALL.

 

[KarlE]

Thank You.  I tried the UnrelatedEmployeeCount formula that you provided and when it came to add the related table/field to the expression, I received an error message saying "The column 'TimeTracking'[FK_Employee] either doesn't exist or doesn't have a relationship to any table available in the current context".  Odd considering I have a direct connection from the Employee Table to the column that I referenced in the formula.

One of my other troubleshooting measures that also uses ALL does provide the expected result of 139, which is leading to part of my confusion.  That DAX formula is:

EmployeeRowCountWithImplicitFilters (Troubleshooting) =

CALCULATE(

    COUNTROWS('Employee'),

    ALL('Employee')

)

Can you provide some more insight why one would work and the other would not?  FYI - I also tried the alternate formula that you provided (EmployeeRowCount = CALCULATE( COUNTROWS('Employee'), REMOVEFILTERS(ALL('Employee')) )) and received an error message that said "The REMOVEFILTERS function expects a table reference for argument '1', but a table expression was used.

Thanks again,

Karl