Back

Explore Courses Blog Tutorials Interview Questions
0 votes
1 view
in BI by (17.6k points)
edited by

I am trying to plot a line chart in powerBI with a reference line based on another column's value. I have data that represents the journeys of different cars on different sections of the road. I am plotting those journeys that travel over the same section of the road. e.g. RoadId 10001.

Distance    JourneyNum    Speed   ThresholdSpeed  RoadId

1           10            50      60              10001

2           10            51      60              10001

3           10            52      60              10001

1           11            45      60              10001

2           11            46      60              10001

3           11            47      60              10001

7           12            20      30              10009

8           12            21      30              10009

9           12            22      30              10009

10          12            23      30              10009

 So currently I have: Distance on the  x-axis (Axis), Speed on the y-axis (Values), JourneyNum as the Legend (Legend), filter to roadId 10001

I want to also add the threshold speed as a reference line or just as another line would do. Any help?

1 Answer

0 votes
by (47.2k points)
  • I don't think it's possible to pass a measure to a constant line, so you'll need a different approach.

  • One possibility is to reshape your data so that ThresholdSpeed appears as part of your Legend. You can do this in DAX like so:

Table2 = 

VAR NewRows = SELECTCOLUMNS(Table1,

                  "Distance", Table1[Distance],

                  "JourneyNum", "Threshold",

                  "Speed", Table1[ThresholdSpeed],

                  "ThresholdSpeed", Table1[ThresholdSpeed],

                  "RoadId", Table1[RoadId])

RETURN UNION(Table1, DISTINCT(NewRows))

  • Which results in a table like this:

Distance  JourneyNum  Speed  ThresholdSpeed  RoadId

1         10          50     60              10001

2         10          51     60              10001

3         10          52     60              10001

1         11          45     60              10001

2         11          46     60              10001

3         11          47     60              10001

1         Threshold   60     60              10001

2         Threshold   60     60              10001

3         Threshold   60     60              10001

7         12          20     30              10009

8         12          21     30              10009

9         12          22     30              10009

10        12          23     30              10009

7         Threshold   30     30              10009

8         Threshold   30     30              10009

9         Threshold   30     30              10009

10        Threshold   30     30              10009

  • Then you make a line chart on this table instead:

Line Chart

  • It's probably preferable to do this transformation in the query editor though so you don't have redundant tables.

Related questions

0 votes
1 answer
asked Dec 26, 2020 in BI by Chris (11.1k points)
0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers!

28.4k questions

29.7k answers

500 comments

94.1k users

Browse Categories

...