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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
karimkz
Advocate I
Advocate I

Add rows / missing date values

Hi all.

 

Recently I found out that my dataset from SQL server lacks some information. My dataset is about machine units and their daily performance. However, if a unit did not work some particular day (maintenance or whatever), the entry with corresponding date is not stored. For example, a CAT unit didn't work on Jan 4th and I would like it to be stored as zero. Here is the example of my database and a modified one. 

My datasetMy datasetTarget datasetTarget dataset

 

 

 

What is the best way to make this modifications? 

 

The initial problem is that the average monthly performance is calculated wrong (it divides total performance by only table-existing days in january, not all 31 days). Make monthly performance in a separate table could make sense, but then I guess it will lack of time intelligence, drill downs, and there are more indicators, not only performance. But if you think it is a better idea, let me know. 

 

I really appreciate any help. 

6 REPLIES 6
v-qiuyu-msft
Community Support
Community Support

Hi @karimkz.

 

In your scenario, you can follow below steps to achieve your requirement:

 

1. Duplicate this query connect to the SQL Server database, filter the original query only keep cat data, rename it as cat table. Also filter the duplicated table to only keep hitachi data, rename it as hitachi table.

2. Create a calendar table.

3. Merge the calendar table with cat table and hitachi table separately. For the unit column, use Fill down or up, for the value column, use Replaces values as 0.

4. Append the cat table with hitachi table.

 

For more information, please check attached .pbix file.

 

 

Best Regards,
Qiuyun Yu

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for your answer! 

 

I see your point, I believe it will work out. Gotta learn how to do all that things though. 

 

Also I think of creating a date table, then do something like:

CALCULATE(SUM(performance); FILTER(date; date.month=EARLIEST(month))).

 

Do you think it will work?

Hi 

 

we also have the same situation , but our tables are very huge so cant make it duplicate , is ithere any way for this issue , did you got the solution

Creating a calendar table and merging it with the existing table is still a best option. 

 

Thanks @karimkz you opened my mind!

You need to dimensionalise your data.  I would create a separte date table and a seperate unit table (with the distinct list of unit types).  You can then use a calculations like this:

 

 

CALCULATE
(	
	AVERAGEX
	(
		CROSSJOIN
		(
			VALUES('Unit'[Unit]),
			VALUES('Date'[Date])
		)
		,SUM([Performance])
	)
)

 

CROSSJOIN will give you the product of all dates and all units that are currently being filtered.  Then AVERAGEX will iterate over this calculating the SUM and then finally calculating the average.  

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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