Update DAX to ensure IDs set as Not known appear as 0 in a SUMX

[DebbieE]

I have some DAX given to me for another project that does a count on IDs in a dim against a fact table

CALCULATE (
   SUMX (
       VALUES ( 'Dim Proj'[Sub Proj ID] ),
       1
   ),
   'Fact Table')

However I have this date

Proj ID   Sub Proj ID

1            1

1            2

1            3

2            None

And At the moment Nones are causing an issue. if I put it into a table 2s arent showing.

I want to be able to update the above DAX to show 0 if Sub Proj ID is none but I can't figure it out.

Can anyone help out with the DAX change?

[PhilipTreacy · Accepted solution]

Hi @DebbieE 

Try this

Measure = IF(SELECTEDVALUE('Dim Proj'[Sub Proj ID]) = "None", 0,
   CALCULATE( SUMX(VALUES('Dim Proj'[Sub Proj ID] ),1), 'Fact Table')

Regards

Phil