Add measure to count not null values but return 0 for null values

[Forthe3kids]

Hi, I have a Power BI measure issue that I can use some assistance in solving.

I want to count number of records within a table that are not null, however I still want records with null values to appear with count of 0. I have tried various Calculate DAX expresssions but so far no luck.

Product table example data

Power Bi report

When I add the measure: Measure  = Count(Products[Sales])   the Sprocket West record no longer shows

If I change the measure to: Measure = COUNTROWS(DISTINCT(Products[Sales]))   The Sprocket West record shows but with the wrong count desired, it is counting rows.

Desired report output would not include the Sales column but should have a measure value of 0 for Sprocket West, not a value of 1 as shown below

Does anyone have any suggestions I can try?

Thanks

[Anonymous]

Hi  @Forthe3kids ,
You can try this measure

Result = 
IF(
    ISFILTERED('Table'[Sales]),
    IF(
        SELECTEDVALUE('Table'[Sales]) <> BLANK(),
        COUNT('Table'[Sales]),
        0
    ),
    CALCULATE(
        COUNT('Table'[Sales]),
        FILTER(
            'Table',
            'Table'[Sales] <> BLANK()
        )
    )
)

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Forthe3kids]

Albert, 

thank you for the reply, unfortunately this did not solve my problem, records with null value for sales do not appear.  Reminder, in the report output i do not want to display the sales nor material columns. I only want product, location and count of sales. This is also a multi table query from an analytical model, thus i cannot transform the data nor use power query and add a column.

Thanks

Dave

[Forthe3kids]

Thank you for thie reply, for my simple example, yes the  "+0" solves the problem.

My appologies, I should have added that the true business case is a report based on an analytic model using multiple tables (Hundred thousand + records). Using the +0 results in a cross-join between the multi-table joins and will produce horrendous performance issues.  

Thank you

[ryan_mayu]

maybe you can try this

Measure  = Count(Products[Sales])  + 0