powerbi directquery distinct count group by issue

Question

I have a table format with record date(including seconds), user ID, Database

I need to show the maximum distinct number of users per hour grouped by every 5 minutes ( not sure if this explains - please see example below)

I am only using DirectQuery storage and not intended to change that to import as well.

I have tried various methods but could not manage without changing the storage mode. Any help is appreciated.

My table sample is,

21/01/2019 12:35:00, jane, UK
21/01/2019 12:35:00, joe, UK
21/01/2019 12:35:00, joe, NL
21/01/2019 12:40:00, bob, NL
21/01/2019 12:40:00, jane, NL
21/01/2019 12:40:00, joe, NL
21/01/2019 12:40:00, jakob, NL

Expected result

21/01/2019 12, UK, 2
21/01/2019 12, NL, 4

OscarLar OscarLar · Accepted Answer · 2019-05-03T11:56:56

Start by adding an hour column in the data table.

Hour = HOUR([DateTime])

Then create a temporary summarized table (this table can be hidden from the report view).

SummTemp = 
SUMMARIZE(
    Data;
    [Datetime];
    [Nat];
    [Hour]; // this is the calculated column with only hour.
    "Date"; DATE(YEAR([DateTime]); MONTH([DateTime]); DAY([DateTime]));
    "Count"; COUNTA('Data'[Nat])
)

Then create a second summarized table:

SummaryTable = 
SELECTCOLUMNS(
    SUMMARIZE(
        SummTemp;
        [Date];
        [Hour];
        [Nat];
        "MaxCount"; MAX('SummTemp'[Count]);
        "NewName"; [Date]&" "&[Hour]
    // OR "NewName"; ( [Date]&" "&TIME([Hour]; 0; 0))*
    );
    "DateHour"; [NewName];
    "Nationality"; [Nat];
    "MaxCNX"; [MaxCount]
)

The end result will look like this:

*If you use this line, then you will get a column in text format but with [dd/mm/yyyy hh:00:00]. You can convert this column to a datetieme format by selecting it and change it from text to datetime via Modeling->Data type. NMot sure if you can drop both minutes and seconds from it but you can drop seconds so that your DateHour column will have a format like: [dd/mm/yyyy hh:00]

powerbi directquery distinct count group by issue

1 Answers