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