Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Count Of Values in New Measure Field from Two Tables

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      v-evelk
      Microsoft Employee

      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
      [email protected]