Create a calculated row for not existing data

[Larsi]

Hi @all,

I have an issue and also an idea how to solve it, but I don't know how to do it. I hope you may help.

Every day all production plants report their status to our database.

Sometimes they forget to report it, especially on weekend.

In Power BI I have the possibility to show also elements without data. The Issue is, that I have calculated a color for each daily status of a plant. All plants with data show a color and the plants with no data are completely empty.

Because of that I would like to create an entry in my table for each plant with no data so that I can set a color for it which is shown in Power BI.

I think this could be possible to realize with a measure.

I have 3 tables: dates, plants, report.

table dates: colums: date, day, month, year - unique date values from all report entries

table plants: columns: id, plantname - unique plantnames

table report: columns: date, plantname, status, color -

The measure:

for each date in table date the measure should check if in table reports at the date are all plantnames from table plants are available. If not, insert a row in table report for the date and the plant.

Thanks for your support!

[tex628]

Correct me if i'm wrong, but essentially what you are looking for is returning a specific color if there is no record in your report table for a specific plant/date?

When you visualize the statuses, are the related to a specific date (Like last recorded status this week?) or do you control it by a slicer?

[Larsi]

In general yes.

Actually I calculate the the color in a separate column.

That's why from my point of view I need to generate to whole row with date if something is missing.

Maybe there's also another solution for this.

Attached you can find an example.

"Indikator" 11 shows no color, there's no data in the database to this plant at this date.

I used backgroundcolor for this solution.