Counting Confusion

[Anonymous]

I created a table to show records that have a count of 2 or more:

But when looking at my tables from the editor, I see these records only once:

What on my table could be causing these records to show a count of 2? I have no filters set on the top table except for companies with an ID that begin with the number 2.

[dramus]

What does the code look like for creating that table?

[Anonymous]

@dramus

It's all SQL:

USE MillenniumLive

SELECT distinct cs.co,cs.service, cs.servicelevel,cs.startdate,cs.endDate,einfo.id,empstatus

FROM MillenniumLive.dbo.cService as cs

INNER JOIN MillenniumLive.dbo.sCompanySetDetail as csd ON csd.co = cs.co

       AND csd.companySet IN ('CBIZ', 'TEAM 1', 'TEAM 2', 'TEAM 3', 'TEAM 4', 'TEAM 5', 'AES', 'CLIENT CONVERSION', 'ACCTMGT','EMS ONLY','TERM')

          CROSS APPLY (SELECT top 1 co FROM MillenniumLive.dbo.CCalendar WHERE co = cs.co AND status = 'PROCESSED') as cc

WHERE ISNUMERIC(cs.co) = 1

       AND cs.service in ('Payroll')

          AND serviceLevel != 'Setup'

       UNION ALL

       SELECT distinct cs.co,cs.service, cs.servicelevel,cs.startdate,cs.endDate,einfo.id,empstatus

FROM MillenniumLive.dbo.cService as cs

INNER JOIN MillenniumLive.dbo.sCompanySetDetail as csd ON csd.co = cs.co

       AND csd.companySet IN ('CBIZ', 'TEAM 1', 'TEAM 2', 'TEAM 3', 'TEAM 4', 'TEAM 5', 'AES', 'CLIENT CONVERSION', 'ACCTMGT','EMS ONLY', 'TERM')

       AND cs.service in ('CBIZ EMS')

          AND serviceLevel != 'Setup'

          order by co

[dramus]

If you create a table visual and just put co=2049 and Service=Payroll in it. Is the count of rows equal to 1 or 2?

[Anonymous]

@dramus It only returns one row when doing that. I guess I have nothing to worry about then? Even when checking my SQL database I'm only getting one result for that record and type. Just confused on how PBI would be seeing 2...

[dramus · Accepted solution]

I can see it returning more than one, based on how the count was created. E.g. if you took the service out, it could produce more than one row. 

Try creating a table with all the columns in it (cs.co,cs.service, cs.servicelevel,cs.startdate,cs.endDate,einfo.id,empstatus), my guess is you have two different SeviceLevels, einfo.id or empStatus, or something.

[Anonymous]

@dramus That was exactly it. The record was in 2 different company sets which I was unaware of. Thank you for your help!