Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Countrows to include and display 0 values

Hello PowerBI community,

 

I did a measure to give a number of status updates per project, for a given period (filter dates). For this I use the COUNTROWS() function and it works well.

 

Now I want to have a list of projects with 0 counts, OR I don't think it's possible with countrows() which is counting only if a row exists - logic...
Can someone advise me for a workaround ?

 

Thank you a lot in advance !

 

General Comment
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Thank you all very much for your answers.

    amitchandaknice one ! I thought it would work but never got a suggestion on-click to see no-data elements for this measure 😕

     

    What finally worked was this :

    Status updates count = VAR statusNb = CALCULATE (
    COUNT ( 'Table'[Status Date] ),
    ALLSELECTED ( 'Table' ),
    VALUES ( 'Table'[Project] )
    )
    RETURN
    IF(statusNb=0, 0, statusNb)
     
    So instead of using countrows(), I am more explicit by using the field "Project", targeting correctly all projects with corresponding names and it gives me even those with 0 counts. It martches with what I egt manually.
    I put this measure in Filters and I locked the filter so it displays only a table with 0 at anytime.
     
    Hope it serves to someone with the same prolem.
    Unfortunately I couldn't find a workaround by using countows().

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You can try Countows(Table)+0  in the  measure or the option, Show item with No date

     

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi Anonymous 

     

    Do you have another table with a list of all projects?

    Can you share a sample of your data in a table format?

     

    Appreciate your Kudos!!

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thank you all very much for your answers.

    amitchandaknice one ! I thought it would work but never got a suggestion on-click to see no-data elements for this measure 😕

     

    What finally worked was this :

    Status updates count = VAR statusNb = CALCULATE (
    COUNT ( 'Table'[Status Date] ),
    ALLSELECTED ( 'Table' ),
    VALUES ( 'Table'[Project] )
    )
    RETURN
    IF(statusNb=0, 0, statusNb)
     
    So instead of using countrows(), I am more explicit by using the field "Project", targeting correctly all projects with corresponding names and it gives me even those with 0 counts. It martches with what I egt manually.
    I put this measure in Filters and I locked the filter so it displays only a table with 0 at anytime.
     
    Hope it serves to someone with the same prolem.
    Unfortunately I couldn't find a workaround by using countows().
  • Icey's avatar
    Icey
    Community Support
    4 years ago

    Hi Anonymous ,

     

    First, please check if what amitchandak mentioned could work in your scenario.

     

    If this doesn't work, maybe there are no complete data for projects and you need to create another table with the complete list of projects. And here is an example: Solved: blanks as 0s, +0 not working as expected - Microsoft Power BI Community.

     

     

    Best Regards,

    Icey

     

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