Forum Discussion
Display two columns on same axis
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.
Unpivoting the data does change how the data is stored. This means you'll need to do one of two things:
- Just check how your other visuals are set up (including any measures) and update for the new format
- 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.
That will only be the case due to how you've related the tables. Give it the same relations as you original table.
It wont let me create a relationship as there is no column with unique values.
This will really fall back onto the overall structure of your tables and project. There will be a way to relate them all together.
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.
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?
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.