gantt like chart, measure help

[blazko]

Hi i want to make Gannt like chart with user activity in time. So I have this table with user id, date, and time of activity, My thought was to change the time to show rough minutes ( zero the seconds), create relation to table iwth minutes only (for the chart purpose), then calculate smth like 'moving average' through out the activity table to calculate -5 minutes backwords, If 0, then no activity for 5 minutes. I want to mark all the 5 minutes and longer non-activites ('breaks'). Have similar measure but on dates table (DATESINPERIOD();LASTDATE();-7;DAY)). And ofc i want the chart for exact (one) user id, and a single day.

I have done this in excel but its too much work to do it daily.

Dont know If my explanation was easy enough to understand. Hope it was 🙂

[v-jiascu-msft · Accepted solution]

Hi @blazko,

Maybe we could add a measure to the visual.

Measure =
VAR test = COUNT ( data[time_h:m] ) RETURN IF ( test = 0, 0, test )

Try this one please:https://1drv.ms/u/s!ArTqPk2pu-BkcJVvCSwYC_HCPkw

Best Regards!

Dale

[MFelix]

Hi @blazko,

Use this custom visuals that is the gantt chart.

Regards,

MFelix

[blazko]

Yes, I know. Tried it. Doesnt seems to work in my scenario.

I need to make smth like this:

[v-jiascu-msft]

Hi @blazko,

According to latest post, maybe we there is a workaround. First try to create a date table with hour and minutes. Then we can use a "Clustered column chart".

1. Create two tables of "hour" and "minute" in excel, then import them into PBI;

2. Create a new table "Final"

Final =
SELECTCOLUMNS (
    CROSSJOIN (
        CALENDAR ( DATE ( 2017, 7, 1 ), DATE ( 2017, 7, 12 ) ),
        'Hour',
        'Minute'
    ),
    "Date", [Date],
    "Time", TIME ( [Hour], [Minute], 0 )
)

3. Create a report using "clustered column chart", group [Time] into bins with width 20";

4. Change the setting of X-axis into "categorical".

 

Best Regards!

Dale

[blazko]

Ok, done. But Im still struggling with putting in my data. Could you help me little more? Can't find the way to add my example .pbix file.

[v-jiascu-msft]

Hi @blazko,

Could you please post a sample here? If it's convenient for you to provide a PBIX file, that would be great. If not, a sample is also good. (tables and their relationship.)

Best Regards!

Dale 

[blazko]

gntt.pbix

[v-jiascu-msft]

Hi @blazko,

Please check it out here: https://1drv.ms/u/s!ArTqPk2pu-Bkb9izproCt0BnlMA

1. I have create a new table "NewMinutes". The old one could lead duplicate values.

2. The relationship has been changed. One new column has been created for establishing relationship. 

Best Regards!

Dale

[blazko]

Hi, thanks for reply 🙂

But its notwhat i wanted. If theres no action in certain minute, it should appear as blank bar. Not count operations in bins (which anyway I find an interesting feature I didnt know of :)). I want to make Gantt like chart. There should be minutes, one by one.

[v-jiascu-msft · Accepted solution]

Hi @blazko,

Maybe we could add a measure to the visual.

Measure =
VAR test = COUNT ( data[time_h:m] ) RETURN IF ( test = 0, 0, test )

Try this one please:https://1drv.ms/u/s!ArTqPk2pu-BkcJVvCSwYC_HCPkw

Best Regards!

Dale

[blazko]

Ok its, working allright 🙂 Thank You. I only changed in the measure count with distinctcount.

I have additional wuestion, any way to invert the chart? Mark only "empty" minutes? Or even better, mark the 5 continues empty minutes, that is 5 minutes without any action?

[v-jiascu-msft · Accepted solution]

Hi @blazko,

Maybe this one:

Measure 2 =
VAR test = DISTINCTCOUNT ( 'data'[time_h:m] ) RETURN IF ( test = 0, 1, 0 )

Best Regards!

Dale

[blazko]

Oh, it was that easy 🙂

And with If statement zeroing the minutes earler than MIN time of activity, and later than MAX time of activity?

[v-jiascu-msft]

Hi @blazko,

I think there isn't a easy way. Because we want the time be continuous and whole, we only can hide them. A visual level filter or a slicer will help.

Best Regards!

Dale