Forum Discussion

tantle61's avatar
tantle61
New Member
2 years ago
Solved

Grouping to include missing values

I am using PBI report builder with row grouping.  I am trying to get a count of 3 catagories within a parent category.  The grouping works if the value is present but I would like to report zero coun...
Need Help
Tutorial Requests
  • tantle61's avatar
    tantle61
    2 years ago

    Found a solution: 

    select id, review_name, updated_dt,

    (select count(*) from
    ara.review_entry re
    where re.review_id = r.id and re.review_decision = 'APPROVED'
    ) as approved,

    (select count(*) from
    review_entry re
    where re.review_id = r.id and re.review_decision = 'REVOKED'
    ) as revoked,

    (select count(*) from
    review_entry re
    where re.review_id = r.id and re.review_decision = 'PENDING'
    ) as pending

    from review r

tantle61's avatar
tantle61
New Member
2 years ago

As in the examples below, the "REVOKED" value is missing from the grouping for "test 123" because this value is missing from the data.  I want to report the zero value as shown in the other image.  I tried this but got the same results as before 

=IIF(Count(Fields!decision.Value, "Review", Recursive) = 0, 0,
Count(IIF(Fields!decision.Value= "REVOKED", 1, Nothing), "Review", Recursive))