Dynamical summarizecolumns or subquery

[Alexandergikalo]

Hi. 
I have table like this:
DateTime,          Cabinet_id, Employee_id
01.01.20 11:00   10               25
01.01.20 11:00   10               12
01.01.20 11:05   10               25
01.01.20 11:05   10               56
01.01.20 11:10   10               null
01.01.20 11:00   12               11
01.01.20 11:05   12               null
01.01.20 11:10   12               12
How can i get bar with result of sql query:
"select hour(DateTime), count(*) from
( select DateTime,Cabinet_id group by DateTime,Cabinet_id)"?

Summarizecolumns and same dax return all the table, but i need to get result after crossfiltering by other elements.

Thank you!

[amitchandak]

@Alexandergikalo 

Take out Hour part in a new column

Hour = Hour([DateTime])

Or

Hour = Format([DateTime],"HH")

The plot any visual take cabinate_id on row/group and take the sum of hours and use it

measure =count(Table[Hours])

Or

measure =Countx(Summarize(Table,Table[Cabinet_id],Table[DateTime],Table[Hour]),[Hour])

[camargos88]

Hi @Alexandergikalo ,

Try creating a calculated column:

Hour = HOUR('Table'[DateTime])

After that:

Ricardo

[Alexandergikalo]

Not that case( 
Sql query will return this tesult
01.01.20 11:00   6 recors
Your count - 8 
I neet to exlude some kind of doubles.

[camargos88 · Accepted solution]

Hi @Alexandergikalo ,

Create the calculated column:

HOUR('Table (2)'[DateTime])

And this measure:

Measure 2 =

COUNTX(SUMMARIZE('Table (2)'; 'Table (2)'[DateTime]; 'Table (2)'[Cabinet_id]; 'Table (2)'[Hour]); 'Table (2)'[Hour])

Ricardo

[Alexandergikalo]

Thanks) I got a solution.