Forum Discussion

SimonJacobs's avatar
SimonJacobs
Helper I
9 years ago

Help Creating A New Table

Hi,   I have a feeling this is pretty basic but can't find instructions on how to do it!   I have a dashboard set up that pulls data from several data files.  All data sources contain a date fiel...
Need Help
aktripathi2506's avatar
aktripathi2506
Helper IV
9 years ago

Senario is not very clear to me but if you want to display weekly data from sunday you can add a new column with following formula:

 

weeknum(date,1) to begine week with Sunday

weeknum(date,2) to begine week with Monday

  • SimonJacobs's avatar
    SimonJacobs
    Helper I
    9 years ago

    Thanks for the reply, however displaying the data's not the issue.  I'm able to filter the data to just show data from Sundays.  what I want is to create a table that has a date column - taken from one of the exisiting tables so the start and end dates correspond - plus the two calculated columns.  I'm fine with the calculations that need to go into the columns, I'm just not sure how to create the table.

     

    • CahabaData's avatar
      CahabaData
      Memorable Member
      9 years ago

      when in data view, use the Modeling tab in the ribbon, select 'New Table'

       

      this will open the formula bar just below the ribbon where you can define your new table i.e.

       

      NewTable = Table1 [DateField]

       

      and this will create that table for you....  if Table 1's date field has repeating dates and you want just unique dates then wrap this with VALUES ( )

       

       

      • SimonJacobs's avatar
        SimonJacobs
        Helper I
        9 years ago

        Thanks CahabaData, I tried your suggestion but got an error message - "A single value for column 'date' in table 'Facebook - key metrics' cannot be determined. this can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count or sum to get a single result.'  I wrapped the the syntax in 'All ()' and it worked except that it added an extra row to the end of the table that was blank.  Do you know why this would be or how I can get rid of it?  It causes problems when I add the calculated columns.

         

    • v-micsh-msft's avatar
      v-micsh-msft
      Microsoft Employee
      9 years ago

      Hi Simon,

       

      I think here you would like to create a reference date table based on the date columns in other tables, right?

      Below is a thread talking about how to generate the date table, check and see if it would meet your requirements:

      Possible Tip/Trick: Dynamic Date Dimension Table

      After creating this date table, under relationship View, create table relationships between this date table and the other tables:

      Create and manage relationships in Power BI Desktop

      Then it should be available to add this one date column in visual with other columns in different tables.

      If any further help needed, please feel free to post back.

      Regards