Forum Discussion
Dynamical summarizecolumns or subquery
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!
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
5 Replies
camargos88Community Champion
Hi Alexandergikalo ,
Try creating a calculated column:
Hour = HOUR('Table'[DateTime])After that:RicardoNot 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
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
- amitchandak
Super User
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])
