Plotting two line charts in one

[Alex_0201]

Hi, not sure if this is possible at all.

But I have two following line charts:

- on the left, MD vs Days, both are regular columns

- on the right, Total Depth vs Total time Column, where Total Depth is a measure and Total time Column is a column created in the Report view.

How do I plot both of them in one line chart?

Thanks in advance!

[TomMartens · Accepted solution]

Hey @Alex_0201 ,

basically, no.

Maybe you can consider creating a measure that "maps" the current value from the axis to the corresponding value of the other x-axis. Similar to this:

Measure = 
var _thecurrentaxisvalue = MAX('tablename'[columnusedonthexaxis])
return
CALCULATE(
    [the other measure]
    , 'theothertable'[theothercolumn] = _thecurrentaxisvalue
)

Hopefully, this provides you with some new ideas on how to tackle your challenge.

Regards,

Tom

[Anand24 · Accepted solution]

@Alex_0201,

Not sure but maybe below workaround might be helpful in your scenario.

You can probably create 2 line charts with backgrounds disabled and overlap them to achieve the formatting in 1 box space.

For 2nd line chart, enable only secondary values.

You'll need to adjust the size of both line charts for your required layout.

You'll get something like this:

In above example:

1st Line Chart: Team vs Sixes(Y-Axis Left Side)

2nd Line Chart: Fours vs Innings(Y-Axis Right side)

Don't forget to give thumbs up and accept this as a solution if it helped you !!!

[amitchandak]

@Alex_0201 , two measures can be plotted together on the same x-axis

[Alex_0201]

@amitchandak sorry, I don't understand your comment...

[TomMartens]

Hey @Alex_0201 , 

@amitchandak just said the same as I already did.

You can use 2 measures/KPIs on in the same plot, if they share the same x-axis.

Regards,

Tom

[RobThrive]

In a line chart, you can put multiple fields into the "Value" section under the visualisation pane.

Probably need to do some work on your axis values to ensure they are consistent though. Try Logarithmic scaling.

[TomMartens]

Hey @Alex_0201 ,

plotting different measures, KPI in one chart is only possible if both measures/KPI share a common x-axis.

Regards,

Tom

[Alex_0201]

Hi @TomMartens, thanks! Any workarounds I can use here? 

[TomMartens · Accepted solution]

Hey @Alex_0201 ,

basically, no.

Maybe you can consider creating a measure that "maps" the current value from the axis to the corresponding value of the other x-axis. Similar to this:

Measure = 
var _thecurrentaxisvalue = MAX('tablename'[columnusedonthexaxis])
return
CALCULATE(
    [the other measure]
    , 'theothertable'[theothercolumn] = _thecurrentaxisvalue
)

Hopefully, this provides you with some new ideas on how to tackle your challenge.

Regards,

Tom

[Alex_0201]

Hi @TomMartens  could you please explain to me what is [the other measure] here?

[TomMartens]

Hey @Alex_0201 ,

as I already mentioned, to draw two lines in a single line chart it's necessary that the numeric values (let's call them measure) share a common axis.

As it easy to draw a single line, it's necessary to use the current axis value to filter the 2nd value accordingly.

For this reason, I create a measure that I call measure, this measure will be used as 2nd value for the line chart

The measure determines the current value of the axis, and stores this value into the variable _thecurrentaxisvalue. This variable will be used to filter the column used as axis in the 2nd line chart. This happens inside the CALCULATE(...).

The reference [the other measure] is just a placeholder that is referencing the numeric expression of the 2nd linechart. As a column with a numeric data type can be simply used in the Values well of a visual, it will be necessary to an aggregation function if this column will be used as numeric expression of the CALCULATE like SUM('tablename'[columnname]

Measure = 
var _thecurrentaxisvalue = MAX('tablename'[columnusedonthexaxis])
return
CALCULATE(
    [the other measure]
    , 'theothertable'[theothercolumn] = _thecurrentaxisvalue
)

Hopefully, this explains the above approach a little better.

Regards,
Tom

[Alex_0201]

Hi @TomMartens  sorry, but I still don't get it. The formula should contain this [the other measure], right? I can't leave it like this. What do I put in there? Should I create another measure for it?

I also get a following error: 

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[Anand24]

Hi @Alex_0201 ,

Seems like [the other measure] is the the 2nd measure for which you want the line chart to be created.

So from the DAX posted by @TomMartens , it seems like below:

1. [columnusedonthexaxis] = your category for which you need to plot both line axis

2. [the other measure] = 2nd numeric value for which line chart is to be plotted

3. [theothercolumn] = 1st numeric value for which line chart is to be plotted

For an example, consider 'Department vs Sales & Units Sold' line chart:

1. [columnusedonthexaxis] = Department

2. [the other measure] = Units Sold

3. [theothercolumn] = Sales

Don't forget to give thumbs up and accept this as a solution if it helped you !!!

[Alex_0201]

HI @Anand24  thanks a lot! Sill get this message:

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[TomMartens]

Hey @Alex_0201 ,

the explanation of @Anand24  is good.

Please consider creating a pbix that contains sample data but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link. If you use an xlsx to create the sample data, upload the xlsx as well.

Regards,

Tom

[Alex_0201]

@TomMartens

 I wish I could recreate the pbix. It is a complex beast, dozens of tables and connections 😣

I sill get this message:

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[TomMartens]

Hey @Alex_0201 ,

here you will find a very simple pbix where I map a numeric column (Amount 2) from Table 2 to the column Column 1 (from Table 1) used as axis:

https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/ESP8nLYaYBdAmuZ0ZnKO4foBVE3fd...

The underlying assumption, all the axis labels from Table 2:Column 2 can be found on Table 1:Column 1, if this is not the case then you have to create a dedicated axis table.

Please post the DAX statement that throws an error.

Regards,

Tom

[Alex_0201]

@TomMartens thank you! The error message was due to missing SUM in the formula.

One tiny problem is that my Amount 2 is the Measure. I've tried to swap it with the Table 1, but Amount 1 is also the Measure. The formula produce a message: 

Column 'Meaure' in table 'DAILY' cannot be found or may not be used in this expression.

Should I replace this Measure with a column? If so, how would you convert this to a column?:

Total depth = ((

var _stepno = SELECTEDVALUE('DM_WELL_PLAN_OP'[step_no])

return

IF(

_stepno=0,

SELECTEDVALUE('DM_WELL_PLAN_OP'[md_to]),

LOOKUPVALUE('DM_WELL_PLAN_OP'[md_to],'DM_WELL_PLAN_OP'[step_no],_stepno+0)

))+SUM(CD_DATUM[datum_elevation]))*(-1)*0.3048

[TomMartens]

Hey Alex,

I can't follow. for this reason, create a simple pbix as I did. Upload the pbix and share the link.

Regards,

Tom

[Alex_0201]

Hi @TomMartens sorry, but these two tables are not connected directly but through another table, and they all contain sensitive info. So it would take me hours and hours to create a pbix out of it, and I still would doubt whether I disclosed too much or not.

Here I tried to provide as much detail as possible and renamed tables so it'd be easier to read the code and understand relation between tables. Hope you would have a bit more patience with me 🙂

I've ended up with this formula, but it returns nothing... 

_Measure =

var _thecurrentaxisvalue = MAX('Table 1'[_Total time Column])

return

CALCULATE(

SUM('Table 2'[_MD])

, 'Table 2'[_days] = _thecurrentaxisvalue

)

Here is what _Total time Column and _MD are, just in case:

_Total time Column = (CALCULATE(SUM('Table 1'[target_duration]),FILTER('Table 1','Table 1'[step_no]<=EARLIER('Table 1'[step_no]))))/24

_MD = CALCULATE(SUM('Table 2'[md_current_convert])*(-1))

[TomMartens]

Hey @Alex_0201 ,

without a pbix it will be difficult to track down the issue.

My assumption _MD and _Total line column are calculated columns.

For this, I recommend adjusting the measure to this

_Measure =
var _thecurrentaxisvalue = MAX('Table 1'[_Total time Column])
return
_thecurrentaxisvalue
/*
CALCULATE(
SUM('Table 2'[_MD])
, 'Table 2'[_days] = _thecurrentaxisvalue
)
*/

With this, you should be able to see a 2nd line in your line chart.

Make sure that these values exist in the column 'Table 2'[_days], make sure that the column and the value have the same data type.

Regards,

Tom