Line Chart Count/Sum/Stdv/etc Of...

[ms5991]

I have two columns in a table, one called JobId, which is unique, and one called StartTime, which is a Date/Time cooresponding to when that job began and is not necessarily unique.  I would like to have a line graph where the x axis is the time, and the y axis is the value of each JobId, so I can see how JobId's are assigned over time.  The problem is that when I put StartTime in the axis box and JobId in the Values box, it only lets me do some sort of calculation on JobId, such as SUM, CountOf, etc.  I want the actual value, but that isn't an option.

I saw another post with this problem, and the given solution was to make sure the data column was of type WholeNumber, but JobId is of that type.  How can I do this? 

[v-sihou-msft]

@ms5991

In this secnario, you can use CALENDAR() function to generate a full calendar date table first. Create another table using GROUPBY() to group by your source table on StartTime column, aggregated with countrows of JobIds. The expression can be like:

Table2= GROUPBY('table','table'[StartTime],"Count Of JobIds",CountRows(currentgroup())) 

Then you can LOOKUPVALUE() the [Count Of JobIds] column into calendar table based on date/time column.

=LOOKUPVALUE('Table2'[Count Of JobIds], 'Table2'[StartTime], 'CalendarTable'[DateTime])

Now you can create a line chart based on this Calendar table. Populate date time on X-Axis, and "Count Of JobIds" in Values.

Regards,

[Greg_Deckler]

You might need to explore a custom visualization for this.

https://app.powerbi.com/visuals/