Forum Discussion

karimkz's avatar
karimkz
Icon for Advocate I rankAdvocate I
9 years ago

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...
Need Help
Tips and Tricks
v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
9 years ago

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

 

 

  • karimkz's avatar
    karimkz
    Icon for Advocate I rankAdvocate I
    9 years ago

    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?

    • Surendra_thota's avatar
      Surendra_thota
      Icon for Helper II rankHelper II
      9 years ago

      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

      • karimkz's avatar
        karimkz
        Icon for Advocate I rankAdvocate I
        9 years ago

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