All Values Are Exactly One Higher Than Expected On Visual

[dylancc96]

Good Evening,

I am stumped on this one. I have created a custom column to get the data I need, and filter that which I do not. 

My Column is as Follows - 

Last PayDate Month = If(EOMONTH(ADPAllSiteWages[PayDate],0) - ADPAllSiteWages[PayDate] < 7, (ADPAllSiteWages[PositionID]))

 [Paydate] - Date

[PositionID] - Text (Count Distinct is being used on the visual)

The purpose of this column is to count the employees on the last paydate (Friday) of each month. In the table view, this seems to perform as expected. However, when I add this column to my visual, each row is exactly one higher than it should be, and the subtotal does not accurately reflect the amounts being shown. 

 

The correct subtotal should be 306 (Verified with external data) and even with the data shown, the correct subtotal would be 314.

 

Has anyone encountered this issue before? From what I can tell, the relationships do not seem to be out of line ( I will re-review in the morning once I've had some sleep.) and I dont see what could cause this from the column formula.

 

Thank you for reviewing, any help or suggestions on this would be greatly appreciated.

 

Best,

Dylan

[dylancc96 · Accepted solution]

Found the Solution after a good nights sleep. Issue was that Count Distinct includes non blank values. For some reason, filtering non blank values at the visual level was not doing anything.

To remedy, I made a measure to distinct count the column I made above, excluding the blanks with the Function DISTINCTCOUNTNOBLANK.

Employee Count (Distinct) = Distinctcountnoblank('ADPAllSiteWages'[Last Paydate Month])

Additional Material -  YT Video on Exclude Blank Records 

How Count Distinct Aggregates (See Answer) 

Hope this helps someone down the road!