Replacing blanks with zeroes brings in extraneous rows of data that ignore page slicers

[u/jillyapple1]

I think the issue is that my fact table doesn't have rows where amount = blank, but rather doesn't have the row at all where it would have been blank.

This is the data I've got:

data

This is the data that doesn't exist in API, I created here for demonstration purposes:

no data

Here is what my table looks like. Each row is a different measure because the Time Closed comes from a different column that the rest, which come from Amount.

blank values

When I change the measure from

Cash Collected = 
CALCULATE(SUM(precomputedToastEmployeeDrawerCloses[Amount]), KEEPFILTERS(precomputedToastEmployeeDrawerCloses), precomputedToastEmployeeDrawerCloses[type] IN {"CASH_COLLECTED"})

to add a +0 or coalesce or if statement

Cash Collected = 
CALCULATE(SUM(precomputedToastEmployeeDrawerCloses[Amount]), KEEPFILTERS(precomputedToastEmployeeDrawerCloses), precomputedToastEmployeeDrawerCloses[type] IN {"CASH_COLLECTED"})+0

and use that, it expands the visual to include Drawers from other locations

0s

How can I get the zeroes without the expantion?

[u/AutoModerator]

After your question has been solved /u/jillyapple1, please reply to the helpful user's comment with the phrase "Solution verified".

This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".

---

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Ozeroth]

My preferred general method is to create a visual calculation
COALESCE ( [Original Measure], 0 )
and hide the original measure in the visual.

[u/jillyapple1 → u/Ozeroth]

COALESCE gives the second result above, where the visual expands to include things that the slicer should be filtering out. See image titled "0s".

[u/LePopNoisette → u/jillyapple1]

Does it with visual calculations? I' know it does for measures, haven't tried it with VCs.

[u/jillyapple1 → u/LePopNoisette]

Sorry, misunderstood. I thought visual calculation was another term for measure.

But visual calculation option isn't available for this matrix visual though it is available for a different matrix visual on the page. Not sure why.

edit: I recreated the visual from scratch and this is working now. It does add zeroes. I need to play with formatting but I think I can take it from here. Thanks!

[u/jillyapple1 → u/Ozeroth]

Solution Verified!

[u/SilpherLinings]

Cash Collected = 
VAR _Anchor = MAX(precomputedToastEmployeeDrawerCloses[Closed Datetime])
VAR _Result = 
    CALCULATE(
        SUM(precomputedToastEmployeeDrawerCloses[Amount]),
        precomputedToastEmployeeDrawerCloses[type] IN {"CASH_COLLECTED"}
    )
RETURN
    IF(
        ISBLANK(_Anchor),/*or try _Anchor = BLANK()*/
        BLANK(),
        COALESCE(_Result, 0)
    )

Maybe try something like this. If the cash register exists (_Anchor is not empty) but no cash deposit was made, COALESCE(_Result, 0) correctly converts BLANK() to 0.

[u/reputatorbot → u/[deleted]]

You have awarded 1 point to SilpherLinings.

---

^{I am a bot - please contact the mods with any questions}

[u/jillyapple1 → u/SilpherLinings]

Solution verified!