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.
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.