Line and Column chart - won't work with the measures I have (but data looks good in table view)

[Anonymous]

I'm working on some Formula1 data and want to show a drivers fastest lap at various circuits, and the lap record at those circuits side by side as a comparison. 

The data looks like this in table view: 

 The page receives a Driver filter from another page drillthrough command, there's also a user filter on year (season) so multiple years can be selected. 

DriverFastestLap = CALCULATE(MIN(F1_LapTimes[time]))

CircuitLapRecord = CALCULATE(MIN(F1_LapTimes[time]),FILTER(F1_LapTimes,F1_LapTimes[CircuitID]=EARLIER(F1_LapTimes[CircuitID])))

I'm trying to display this in a stacked column chart, or a line and column but when I add the CircuitLapRecord to the Values box it automaticaly changes to 'Count of...'. I can't add the DriverFastestLap to the values box at all. 

Apologies, I'm fairly new at PowerBI and this one has me stumped!

Thanks, 

Nick. 

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could use split to configure your time value.

Then you could use splitted column 1 *60+splitted column 2 to get final time.

[HotChilli]

What is the datatype of the CircuitLapRecord?

[Anonymous]

Ah - it's a Text data type, which I guess is my problem. 

However, if I try to convert it to time, I get the following error: 

[HotChilli]

The powerbi engine doesn't like the conversion to a time (not a timespan - it's trying to convert to 1:22am with .683 secs)

The power query engine is more flexible but I don't think it's going to give you what you want which is a duration in h,m,s (which can be used in powerbi front end, manipulated mathematically and displayed correctly). Power Query has a duration type but power bi doesn't.

I see that you have a Duration column which is a decimal and this is probably your best option to use for the axis.  You could put the text circuitlaprecord column in the tooltips section of the chart so it's still available.