Count Of Values from Two Table

[TalhaTJ]

Hello,
I need to create a New Measure filed to list count of No error (Nil Error) Projects with Monthly basis.
I am having two Table, 1. Version Table 2. QC Error Table.
Schema for Version Table is:
     [Id]
     ,[Project Name]
     ,[Project_ID]
    ,[Version]
    ,[Type]
    ,[Active]

and Schema for QC error Table is:
   [ID]
   ,[Staff]
   ,[Project_ID]
    ,[Version_ID]
   ,[Type Of Error]

for getting the above , I Used following SQL query and created a New views in SQL DB.

SELECT        V.[Project Name], COUNT(Q.Project) AS [Count of Version ID], V.[Project ID at SMA], V.[Final Completion Date] V.Id AS [Version ID]
FROM            dbo.[QC Errors] AS Q RIGHT OUTER JOIN
                         dbo.[Version Table] AS V ON Q.Version = V.Id AND Q.[Type Of Error] = N'Error, Customer Reported'
GROUP BY V.[Project Name], V.[Project ID at SMA], V.[Final Completion Date]

i  got the query of Output as below

Project ID     Count of Version ID
Project1        25
Project2        0
Project3        2
Project4        0

its shows the all Project with count of versionID available in QC error Table, If Version ID is Not available it shows the ZERO Value.

i need to get only the zero Error Projects from this View (i need the project name which are having no error) in Power BI.

then i import this view to Power BI and i filter the value with Monthly Basis (Using Final Completion Date filed ) and error count = 0

but i am getting same value in all rows.

Date         count of No error project.

Jan            433

feb            433

Mar          433 .....

i tried this in PowerBI New Table command.

Table 2 = GENERATEALL(SUMMARIZE('Version Table','Version Table'[Project ID at SMA],'Version Table'[Id]),SUMMARIZE('QC Errors','QC Errors'[Version],"TestColumn",COUNTX('QC Errors',Value('QC Errors'[Version]) = 'Version Table'[Id])))

But i am getting following error in expression.

can anybody help me to resolve this.

Thanks
TalhaTJ

[v-lid-msft · Accepted solution]

Hi @TalhaTJ ,

If the date is related to the QC Error table, we can use the following measure to meet your requirement.

count of No error project =
COUNTX (
    ALL ( 'Version' ),
    IF (
        CONTAINS ( 'QC Errors', 'QC Errors'[Project_ID], [Project_ID] ),
        BLANK (),
        1
    )
)

If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attachment.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.