Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Fenrir
New Member

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

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 129.8.202212,4
Loading Place 229.8.202220,6
Loading Place 329.8.202210
Loading Place 429.8.20226,8
Loading Place 529.8.202235,2


But what I am trying to achieve is this:

Loadingname        Loadingdate  Amount
Loading Place 129.8.202212,4
Loading Place 229.8.202220,6
Loading Place 329.8.202210
Loading Place 429.8.20226,8
Loading Place 529.8.202235,2
Loading Place 629.8.20220
Loading Place 729.8.20220


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? 🙂

1 ACCEPTED SOLUTION
Fenrir
New Member

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. 🙂

View solution in original post

2 REPLIES 2
Fenrir
New Member

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
New Member

(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.

 

Fenrir_1-1661773019052.png

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors