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

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?

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

sharc316

 

Hi,

Maybe something isn't clear here. In the lastest method, we don't need to unpivot any columns. The relationships are created between drivers. Blank values won't have an effect. You should a table of all the unique drivers. The relationships would be Driver->Driver #1 and Driver ->Driver #2. The cardinality is 1:many. We can have blank values in Many-side.

 

This method won't change the structure of table. So the other visuls won't be messed up. You could have a try.

  • sharc316's avatar
    sharc316
    Helper I
    9 years ago

    hmm ok I see what you're saying. I'm goint to need a way to pull the driver #'s from the two columns into one for the new table automatically so I dont have to add everytime there is a new driver. I'll give it a try. Thanks very much for your help.

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

    My pleasure. That could be an annoyance. Maybe it's easy to do it in an Excel. Firstly, creating a table of drivers in Excel, then import to Power BI. Everytime new drivers added, you can refresh from Power BI. Adding driver in excel would be easier. Append Driver #2 to Driver #1, remove duplicates. Hope this could be a little help.