Count Of Values in New Measure Field from Two Tables

[TalhaTJ]

Hello,
I need to create a New Measure filed to list count of No 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 .....

Can any one help me to resolve this issue.
Thanks
TalhaTJ

[TalhaTJ]

Hi all,

anyone having idea to resolve the above issue.

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

[v-evelk]

Hi guys,

Unfortunately, DAX is out of my expertise, at least for a while.

However, you could ask in more suitable threads as that one and another one.

Kind Regards,

Evgenii Elkin,
Software Engineer
Microsoft Power BI Custom Visuals
pbicvsupport@microsoft.com