Case Property Issue with Power BI

Users
1

I have a Power BI report that shows case data, meaning the latest reported value for our various indicators. When someone goes to report against indicator, they are able to select one, all or anything in between when they report. So, if there are 6 indicators and someone submits reporting for 1 and 2, then my Power BI report shows data for 1 and 2 but then nothing for 3-6. However, what I think is happening, is that if someone reports on 1 and 2 and then a few days later, reports on 3-6 (but doesn't report anything again for 1 and 2), the case data is wiping out what was reported for 1 and 2 because the latest submission included reporting from 3-6.

For example, on this indicator (see attached), you can see a value was reported on 4/22 but then there are 4 more data submissions for other indicators part of the same RBM, but then technically, no value is reported on this original indicator and therefore, the value isn't populating on my case value Power BI report.

Capture
Capture754×348 34.2 KB

The 34 value should be what's showing up on my case reports, but instead I'm getting a blank. Any suggestions on how to resolve or work around this? I have form reports too which aren't affected, but I like this case report because I want to be able to show a current values only report (or current latest values).

2

you want to check if the case property HAS a value on the case already
if there's a value
use it.
if not, use the one in the form.
if neither, the one in the form will do - it'll stay blank

you can use this function to find out if the case property is empty
https://dimagi.atlassian.net/wiki/spaces/commcarepublic/pages/2143957589/CommCare+Functions#coalesce

so, your case property should update from a hidden calculation NOT the question itself - trust me on this because hidden elements don't get a default value. so if the question does not show up, it won't pick up a value.

hidden_calc_for_case_property1 = coalesce(case_property, form_question)

in case management
use hidden_calc_for_case_property1 to update case_property1.

NOW here's another thing to pay attention to: do you care about the date that the old value was entered?
if so, you might want to save some dates as properties as well or something.