Forum Discussion

Forthe3kids's avatar
Forthe3kids
New Member
2 years ago

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

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 ap...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Forthe3kids
New Member
2 years ago

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