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 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

Need Help

4 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    2 years ago

    maybe you can try this

    Measure  = Count(Products[Sales])  + 0

  • Forthe3kids's avatar
    Forthe3kids
    New Member
    2 years ago

    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

  • 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