Forum Discussion
Time line with data?
I have found a great solution with DAX in Power BI. Will try to walk you through it:
1. I have a table with data, that has a "startdatetime" and "enddatetime". And i want to make a historical view of these, and count how many is "active" each hour.
2. I create a table for date and time structure like this (as an example i found on this forum):Dates = SELECTCOLUMNS( CROSSJOIN( //************************************************************ // Date range below. This will generate a table with a [Date] column //************************************************************ CALENDAR(min(LivedataAkuten[KONTAKTSTARTDATUM]);max(LivedataAkuten[KONTAKTSTARTDATUM])); //************************************************************ // Time granduality below. This will generate a table with a [Time] column //************************************************************ SELECTCOLUMNS( CROSSJOIN( SELECTCOLUMNS(GENERATESERIES(0;0;1);"Second";[Value]); SELECTCOLUMNS(GENERATESERIES(0;0;1);"Minute";[Value]); SELECTCOLUMNS(GENERATESERIES(0;23;1);"Hour";[Value]) ); "Time"; TIME([Hour];[Minute];[Second]) ) ); //************************************************************ // Custom table columns below. // This is extracted from the Date and Time columns generated above //************************************************************ "Date"; FORMAT([Date]; "YYYY-MM-DD"); "Time"; FORMAT([Time]; "hh:mm:ss"); "DateTime"; [Date]+[Time] )3. I create a new table with dates, and groupinginformation like this
AktivaSamtidigt = CROSSJOIN(Dates;DISTINCT(Table[GroupOfAccounts]))
4. In that table i add a column with this script
Antal = VAR Antalet = COUNTX(FILTER(Table;Table[STARTDATE]<=AktivaSamtidigt[DateTime]&&Table[ENDDATE]>AktivaSamtidigt[DateTime]+TIME(1;0;0)&&Table[GroupOfAccounts]=AktivaSamtidigt[GroupOfAccounts]);Table[ContactID]) RETURN IF(ISBLANK(Antalet); 0; Antalet)
From this I can then create a visual presentation like this:
This is working way faster than scripting with the crossjoin as I did before!
So! That's a good friyay evening!
Hi Anonymous
I apologise if i haven't quite grasped your description full and this is a bit off the cuff but...
What about adding a column to your table which is case 1 or 0 depending on the relevant hour and it being active or not.
Then you should be able to do a count in Power BI?
Hi there!
It might be me being bad at explaining. :)
To do the same thing in T-SQL i had to do like in the script here.
I "loop" through days, and then the hours on those days, and for each "row" of day and number, i do a count script on the posts that is between those datetimes..
It is this script that i feel that powerbi might be able to fix more elegant.
SELECT cast(d.DATUM as date) Datum, cast(CONCAT(B.TIMME,':00') as time) Timme, Antal FROM D CROSS APPLY ( SELECT TIMME, Antal FROM T T CROSS APPLY ( SELECT ( SELECT count(*) FROM vTF WHERE vTF.KONTAKTSTARTDATUM <= CAST(CONCAT(convert(date,D.DATUM),' ',T.TIMME,':00') as datetime) -- Kontakten startad AND vTF.KONTAKTSLUTDATUM > dateadd(hour, +1, CAST(CONCAT(convert(date,D.DATUM),' ',T.TIMME,':00') as datetime)) -- Men ej avslutad ) as Antal ) A ) B ORDER BY D.DATUM, TIMME
The output from this script would be.
DATUM, TIMME, Antal
2019-10-25, 12:00, 5
2019-10-25, 13:00, 7
2019-10-25, 14:00, 4
I have found a great solution with DAX in Power BI. Will try to walk you through it:
1. I have a table with data, that has a "startdatetime" and "enddatetime". And i want to make a historical view of these, and count how many is "active" each hour.
2. I create a table for date and time structure like this (as an example i found on this forum):Dates = SELECTCOLUMNS( CROSSJOIN( //************************************************************ // Date range below. This will generate a table with a [Date] column //************************************************************ CALENDAR(min(LivedataAkuten[KONTAKTSTARTDATUM]);max(LivedataAkuten[KONTAKTSTARTDATUM])); //************************************************************ // Time granduality below. This will generate a table with a [Time] column //************************************************************ SELECTCOLUMNS( CROSSJOIN( SELECTCOLUMNS(GENERATESERIES(0;0;1);"Second";[Value]); SELECTCOLUMNS(GENERATESERIES(0;0;1);"Minute";[Value]); SELECTCOLUMNS(GENERATESERIES(0;23;1);"Hour";[Value]) ); "Time"; TIME([Hour];[Minute];[Second]) ) ); //************************************************************ // Custom table columns below. // This is extracted from the Date and Time columns generated above //************************************************************ "Date"; FORMAT([Date]; "YYYY-MM-DD"); "Time"; FORMAT([Time]; "hh:mm:ss"); "DateTime"; [Date]+[Time] )3. I create a new table with dates, and groupinginformation like this
AktivaSamtidigt = CROSSJOIN(Dates;DISTINCT(Table[GroupOfAccounts]))
4. In that table i add a column with this script
Antal = VAR Antalet = COUNTX(FILTER(Table;Table[STARTDATE]<=AktivaSamtidigt[DateTime]&&Table[ENDDATE]>AktivaSamtidigt[DateTime]+TIME(1;0;0)&&Table[GroupOfAccounts]=AktivaSamtidigt[GroupOfAccounts]);Table[ContactID]) RETURN IF(ISBLANK(Antalet); 0; Antalet)
From this I can then create a visual presentation like this:
This is working way faster than scripting with the crossjoin as I did before!
So! That's a good friyay evening!
Great going!