Forum Discussion

sharc316's avatar
sharc316
Helper I
9 years ago
Solved

Display two columns on same axis

Hi,   Wondering if there is a way to display data from columns on one axis. See screenshot below; i have driver 1 and driver 2 columns and i would like to display miles driven by each driver. Each ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi, sharc316,

     

    Anonymous has given a good advice. Follow the steps below, you would get what you want.

    1. Open Query Editor, add a conditional column as the image 1 showed. Change the type of column "IfNull" into Whole Number.

    2. Choose columns "Driver #1" and "Driver #2" at the same time, and then click "Unpivot Column". You will see the result. Rename the last column to "Driver".

    3. Create a measure. 

    TotalMiles =
    SUMX (
        Table1,
        IF ( 'Table1'[IfNull] = 1, 'Table1'[Miles] / 2, 'Table1'[Miles] )
    )

    It's done now. You can have a try.

     

     

  • Anonymous's avatar
    Anonymous
    9 years ago

    Unpivoting the data does change how the data is stored.  This means you'll need to do one of two things:

     

    1. Just check how your other visuals are set up (including any measures) and update for the new format
    2. Instead of unpivoting the original table, make a copy of it in the 'Edit Queries' section, and make it a new table.  This will allow you to have visuals using the old way if you have no other option.
sharc316's avatar
sharc316
Helper I
9 years ago

Just realized that the date filter I have on my report will not work on this tile because it's from a different table. Any other solution to this? Maybe some way to use the original table without unpivoting?

Anonymous's avatar
Anonymous
Not applicable
9 years ago

That will only be the case due to how you've related the tables.  Give it the same relations as you original table.

  • sharc316's avatar
    sharc316
    Helper I
    9 years ago

    It wont let me create a relationship as there is no column with unique values.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      This will really fall back onto the overall structure of your tables and project.  There will be a way to relate them all together.

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

      sharc316

       

      Hi, I am so glad to tell you I really found an easy way. You can have a try.

      1. We need a table with all the drivers. (here we have a table Drivers)

      2. Create two relationships between Drivers and Table1 (here we have), only one is active.

      3. Create a calculated column in Table1.

      IfNull =
      IF ( ISBLANK ( Table1[Driver #2] ), 0, 1 )

      4. Create a measure.

      TotalMiles1 =
      SUMX ( Table1, IF ( Table1[IfNull] = 0, Table1[Miles], Table1[Miles] / 2 ) )
      + CALCULATE (
      SUMX ( Table1, IF ( Table1[IfNull] = 1, Table1[Miles] / 2, 0 ) ),
      USERELATIONSHIP ( Table1[Driver #2], Drivers[Driver] )
      )

      There will be a little by-product (a blank driver), but it won't do any harm.

       

       

      • sharc316's avatar
        sharc316
        Helper I
        9 years ago

        v-jiascu-msftThank you for taking the time to provide such detailed instructions. I'm afraid that my situation is a bit more complicated. I have an appended table from where most of my visuals are pulled from. I've duplicated that able so that I can unpivot the Driver #1 and Driver #2 columns. When I did that in the original appended table it messed up almost all other graphs.

         

        When the tables were appended there were a lot of fields that were not part of other tables which would be blank for a lot of the rows and because of this reason no relationships can be established.

         

        I think the only way to get a primary key here would be to create a separate column and just populate it with row counts for as many rows as there is data and for any new data that comes out every week. Would my approach be somewhat accurate here?