Forum Discussion

ms5991's avatar
ms5991
Microsoft Employee
10 years ago

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

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 h...
Need Help
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
10 years ago

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,