How to set default value to the table when there is no data existing at all?

[Fenrir]

Hello all,

After couple months of practicing using Power Bi (which is pretty staggeringly neat tool) I need to make my very first question which I seem to be unable to solve on my own. The case is quite simple, or so I at first boldly believed.. 🙂

So, I have this hypothetical table vw_shipments. What I'd basically need to archieve is list of the regular loading places, whether there is anything to load today or not.

So the current challenge is that I'd technically need to set default values for data which does not even exist in the table.
I mean, if there's nothing to load/booking today, there's naturally no data in database regarding that certain loading place.

I've tried many functions such as...

-IF(vw_shipments[Amount] >0, "True")
-IF(ISBLANK(vw_shipment[Amount]), 0)
-COALESCE( SUM(vw_shipments[Amount]), 0)

..but of course they do not work. Power Bi has already filtered out those Loading places which do not have anything, so these are not viable solutions. And due to this checking "Show items with no data" does not naturally work either.

To get a bit more visual with this basically the only result I am able to get is this:

Loadingname	Loadingdate	Amount
Loading Place 1	29.8.2022	12,4
Loading Place 2	29.8.2022	20,6
Loading Place 3	29.8.2022	10
Loading Place 4	29.8.2022	6,8
Loading Place 5	29.8.2022	35,2

But what I am trying to achieve is this:

Loadingname	Loadingdate	Amount
Loading Place 1	29.8.2022	12,4
Loading Place 2	29.8.2022	20,6
Loading Place 3	29.8.2022	10
Loading Place 4	29.8.2022	6,8
Loading Place 5	29.8.2022	35,2
Loading Place 6	29.8.2022	0
Loading Place 7	29.8.2022	0

How would I force these 7 Loadingname-values to stick visible, whatever the Loadingdate is, Amounts are etc?

I guess that in the background I'd need to set Loadingdate to be relative time and maybe be "in this month" or such, and define the Loading date to be Today() using a manually created function. Or would it be the best if I'd create a separate table for these 7 loading places?

Ideas? 🙂

[Fenrir · Accepted solution]

Okay, to answer to myself, I think I got this.

I did test this out and created a separated table for the default values like this:

vw_defaultloadingplaces = {
(1, "LOADING PLACE 1", 0, 0, DATEVALUE(TODAY())),
(2, "LOADING PLACE 2", 0, 0, DATEVALUE(TODAY())),
(3, "LOADING PLACE 3", 0, 0, DATEVALUE(TODAY())),
(4, "LOADING PLACE 4", 0, 0, DATEVALUE(TODAY())),
(5, "LOADING PLACE 5", 0, 0, DATEVALUE(TODAY())),
(6, "LOADING PLACE 6", 0, 0, DATEVALUE(TODAY())),
(7, "LOADING PLACE 7", 0, 0, DATEVALUE(TODAY()))
}

Then I built up a visual based on this table. At first I though I need to create measures to sum those 0 values with Amounts etc. but it actually seem to work without doing so too (since loadingdate is filtered with relative date is this date and loading place names are identical and connected using manage relationships).

So now I got the table visual which does show the 0 values too.

There's certain relationships I need to create in order to make it fully compatible with the existing data (different unit types mess it up etc), but this actually was a working solution for it.

Thanks anyway!

Hopefully this helps someone else with similar challenge someday. 🙂

[Fenrir]

(sorry, I managed to delete my own solution since it was under this one, what a confusing forum)

Next issue is that totals are messed up and I am not completely sure why. I would understand if Power Bi would gather all the historical data regarding the amounts (per Loading place), but it is impossible since amount is way too low for it but at the same time it's wayyy too much since total amounts is only approx. 6,4% of the totals shown in visual.

Is there any bullet-proof ways to calculate only the values shown in visual? I tend to have this issue fairly often.