Measure returning all missing instead of projects missing baseline 0

[deeave]

I'm attemtping to return a value of 1 when baselinenumber 0 does not exist in the projects baseline 2 table.  The below measure is showing all projects do not have a baseline 0 when it should only show 15 that do not have one.  

COUNTROWS (

    FILTER (

        'projects data2',

        NOT(             'projects data2'[projectid] IN

            CALCULATETABLE (

                VALUES ('projects baseline2'[projectid]),

                'projects baseline2'[baselinenumber] = 0

            )

        )

    )

)

[Ashish_Mathur]

Hi,

Share data of the 2 tables in a format that can be pasted in an MS Excel file and for those 2 tables, show the expected result.

[deeave]

The excel shows the two fields.  Project ID and Baselinenumber.  In the Projects Baseline table, the ProjectID can have Baselinenumber 0, 1, 2, 3, etc.  Baselinenumber 0 is the primary baseline used for project schedules and we want to show in the table with the X's what projects DO NOT have a baseline 0.  We don't care about the 1, 2, 3, etc.  Here is my SQL query that is successful at returning the correct projects.

SELECT DISTINCT d.projectnumber, d.projectname
FROM [dbo].[Projects Data2] d
WHERE d.projectnumber is not null
and d.projectstatus = 'active'
and NOT EXISTS ( 

SELECT 1
FROM [dbo].[Projects Baseline2] b
WHERE b.ProjectID = d.ProjectID
AND b.BaselineNumber = '0');

[Ashish_Mathur]

Hi,

Write this measure

Measure = calculate(distinctcount(Data[ProjectID]),Data[baselinenumber]<>0)

Hope this helps.

[amitchandak]

@deeave , Try like

COUNTROWS (summarize(Filter( 'projects baseline2','projects baseline2'[baselinenumber] = 0 ), 'projects baseline2'[projectid]))