CODEWITHSUNDEEP
powerbi
Daniel Wyatt
Daniel Wyatt

Reputation: 1151

Add a dynamic constant line based on column in powerbi

I am trying to plot a line chart in powerBI with a reference line based on another columns value. I have data that represents the journeys of different cars on different sections of road. I am plotting those journeys that travel over the same section of 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 x-axis (Axis), Speed on y-axis (Values), JourneyNum as the Legend (Legend), filter to roadId 10001

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

Upvotes: 3

Views: 5986

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

I don't think it's possible (yet) 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

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

Upvotes: 3

Related Questions