Forum Discussion

slink9's avatar
slink9
Frequent Visitor
9 years ago
Solved

Duplicate PivotChart Functionality

Hi, I have a solution that was developed using Access 2010. The data is entered using a form or it can be imported from an Excel sheet. The user will then pull up Chart view and can drag headers fro...
  • dkay84_PowerBI's avatar
    dkay84_PowerBI
    9 years ago

    It is impossible to give "exact steps" without sample data.

     

    Can you provide a screenshot of your underlying data structure?  Below is the structure you will need to create to get three lines in a chart that represent individual calendar years:

     

    Month+Date Key |  2012 data  |  2013 data  |  2014 data  |  Date Axis Sort Order

    Jan 1                          x                      x                   x                         1

    Jan 2                          x                       x                   x                        2

    Jan 3                          x                      x                    x                        3

    ...

    etc.

     

    Then you would create a line chart and put the Month+Date Key as the axis.  Sort the Month+Date Key field by the Date Axis Sort Order column (under the Modeling tab I believe).  Then add the 2012, 2013, and 2014 fields to your Values area and they will share the common axis but each represent a separate year.

     

    If you want to only display 1 year at a time and have a slicer for year, then the table structure needs to be different, or you have to do some more complex DAX which is probably not a good choice given your challenges so far.

GarryFarrell's avatar
GarryFarrell
Icon for Advocate III rankAdvocate III
9 years ago

Hi,

 

Have you upgraded the Access databse from 2010 to 2016? I think you just open the database in 2016 and it will prompt you to upgrade.

 

Next step is to work out if Access 2016 is 64bit or 32bit as someone else mentioned. Then download the matching Power BI Desktop software. Then make sure you can make a Then write back on this topis to tell everyone which versions you are using. If they match my versions I will make a sample PBX file if you email me the Access DB.

 

Regards,

Garry

  • slink9's avatar
    slink9
    Frequent Visitor
    9 years ago

    The actual database version is 2003+, but it works fine in Access 2010. If I upgrade the db itself to 2016, will it support the current 2010/2016 machines that we have in place?

     

    I have PowerBI on a separate Win10 VM that we have set up. I am able to connect to the database fine, after removing the password. Am I missing functionality that would be provided by PowerBI if it the database itself were updated to 2016?

     

    The VM is running Win10Pro 64bit and the PowerBI (PBIDesktop.exe) version is 2.41.4581.361. The only indicator that it is a 64bit version of the software is that it is in the Program Files directory.

    • GarryFarrell's avatar
      GarryFarrell
      Icon for Advocate III rankAdvocate III
      9 years ago

      Hi,

       

      MS Access is generally not backwards compatible. So upgrading the 2016 will stop another accessing the database using older versions.

       

      There is no need to upgrade the database. You should be able to connect with the correct driver installed.

       

      There is no functionality in Power BI that use will miss out on by not upgrading. I though it would be best to have the database on the latest version but that's up to you.

       

      Did you manage to create a chart for your user?