topic Re: Count Of Values in New Measure Field from Two Tables in Custom Visuals Development Discussion

Count Of Values in New Measure Field from Two Tables

Anonymous — Mon, 09 Sep 2019 07:13:42 GMT

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

Re: Count Of Values in New Measure Field from Two Tables

Anonymous — Tue, 10 Sep 2019 06:19:42 GMT

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

Re: Count Of Values in New Measure Field from Two Tables

v-evelk — Tue, 10 Sep 2019 15:12:06 GMT

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