Forum Discussion
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
- AnonymousNot 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-evelkMicrosoft 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]