Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hallo,
I need to group the table
1) a column: grouped by date
2) second column: grouped by hours
3) 3d coumn: count distinct for each hour how many operators I have for each hour
4) 4 column: count nr CONTENITORI for each hour
Here an example of the table:
MODULO | MENU | CONTENITORE | INIZIO | MATRICOLA |
Replenish | .Reappro palette | 436000000011889745 | 10 ago 20 06:05:40 | SHER |
Replenish | .Reappro palette | 436000000011785597 | 10 ago 20 06:07:27 | SEIB |
Replenish | .Reappro palette | 436000000010710972 | 10 ago 20 06:08:51 | SHER |
Replenish | .Reappro palette | 436000000011920455 | 10 ago 20 06:11:07 | SEIB |
Replenish | .Reappro palette | 436000000011843730 | 10 ago 20 06:11:33 | EMAT |
Replenish | .Reappro palette | 436000000011867972 | 10 ago 20 06:12:35 | SHER |
Replenish | .Rgt pkg manuel | 436000000011815690 | 10 ago 20 06:13:54 | SINN |
Replenish | .Reappro palette | 436000000007795449 | 10 ago 20 07:15:16 | SEIB |
Replenish | .Rgt pkg manuel | 436000000011329012 | 10 ago 20 07:14:06 | ASTL |
Replenish | .Reappro palette | 436000000011749926 | 10 ago 20 07:15:23 | EMAT |
Replenish | .Reappro palette | 436000000011220098 | 10 ago 20 07:17:28 | SEIB |
Replenish | .Reappro palette | 436000000011923883 | 10 ago 20 07:20:01 | SHER |
Replenish | .Reappro palette | 436000000011918308 | 10 ago 20 07:19:46 | EMAT |
Replenish | .Reappro palette | 436000000011792830 | 10 ago 20 07:21:35 | SEIB |
Replenish | .Reappro palette | 436000000011915222 | 10 ago 20 07:22:27 | ASTL |
Replenish | .Reappro palette | 436000000011500732 | 10 ago 20 07:23:42 | EMAT |
Replenish | .Rgt pkg manuel | 436000000011524585 | 10 ago 20 07:24:00 | EBOJ |
Replenish | .Reappro palette | 436000000010925635 | 10 ago 20 07:27:03 | SEIB |
Replenish | .Rgt pkg manuel | 436000000011663017 | 10 ago 20 07:28:24 | EBOJ |
Replenish | .Rgt pkg manuel | 436000000011844041 | 10 ago 20 07:20:00 | MAMI |
Replenish | .Rgt pkg manuel | 436000000011804472 | 10 ago 20 06:32:51 | DEKA |
Replenish | .Reappro palette | 436000000011841484 | 10 ago 20 06:36:19 | ASTL |
Replenish | .Reappro palette | 436000000010720605 | 10 ago 20 06:39:43 | SEIB |
Replenish | .Rgt pkg manuel | 436000000011024337 | 11 ago 20 06:30:02 | EBOJ |
Replenish | .Reappro palette | 436000000011899997 | 11 ago 20 06:44:18 | SEIB |
Replenish | .Reappro palette | 436000000011483202 | 11 ago 20 06:41:31 | ASTL |
Replenish | .Reappro palette | 436000000010064051 | 11 ago 20 06:45:07 | EMAT |
Replenish | .Rgt pkg manuel | 436000000011810305 | 11 ago 20 06:46:14 | DEKA |
Replenish | .Reappro palette | 436000000011548604 | 11 ago 20 06:46:35 | SHER |
Replenish | .Rgt pkg manuel | 436000000011143458 | 11 ago 20 06:43:20 | EBOJ |
Replenish | .Rgt pkg manuel | 436000000011683084 | 11 ago 20 06:50:13 | EBOJ |
Replenish | .Reappro palette | 436000000011843662 | 11 ago 20 06:52:53 | EMAT |
Replenish | .Rgt pkg manuel | 436000000011783333 | 11 ago 20 06:59:53 | ABMA |
Replenish | .Reappro palette | 436000000010683931 | 11 ago 20 06:50:00 | SHER |
Replenish | .Reappro palette | 436000000011662225 | 11 ago 20 06:59:11 | SEIB |
Replenish | .Reappro palette | 436000000008626728 | 11 ago 20 06:58:13 | EMAT |
Replenish | .Rgt pkg manuel | 436000000011914621 | 11 ago 20 06:59:55 | EBOJ |
Replenish | .Rgt pkg manuel | 436000000011792557 | 11 ago 20 06:47:21 | DEKA |
Replenish | .Reappro palette | 436000000008960372 | 11 ago 20 07:04:04 | SEIB |
Replenish | .Rgt pkg manuel | 436000000011777035 | 11 ago 20 07:04:21 | EBOJ |
Replenish | .Rgt pkg manuel | 436000000011909764 | 11 ago 20 07:05:17 | DEKA |
Replenish | .Rgt pkg manuel | 436000000011442971 | 11 ago 20 07:13:18 | EBOJ |
Replenish | .Reappro palette | 436000000011400698 | 11 ago 20 07:15:22 | ASTL |
Replenish | .Reappro palette | 436000000011873485 | 11 ago 20 07:12:55 | SEIB |
Here the output I'm looking for to get:
date | hour | nr op | nr contenitori |
10/08/2020 | 6 | 6 | 10 |
10/08/2020 | 7 | 6 | 13 |
11/08/2020 | 6 | 7 | 18 |
11/08/2020 | 7 | 4 | 6 |
Do you have any formulas to solve it?
Solved! Go to Solution.
@Anonymous , Try like this
New column
Date =Table[INIZIO].date
hour =hour(Table[INIZIO])
New Table
summarize(Table, Table[Date], Table[hour], "op nr", distinctcount(Table[MATRICOLA]), "contenitori",count(Table[CONTENITORE]))
Hi @Anonymous ,
You can create below 2 calculated columns and a calculated table to achieve it:
1. Create calculated columns to get the date and hour
Date = DATE(YEAR('Table'[INIZIO]),MONTH('Table'[INIZIO]),DAY('Table'[INIZIO]))
Hour = HOUR('Table'[INIZIO])
2. Create a summary table
Summary =
SUMMARIZE (
'Table',
'Table'[Date],
'Table'[Hour],
"Nr op", CALCULATE (
DISTINCTCOUNT ( 'Table'[MATRICOLA] ),
FILTER (
( 'Table' ),
DATE ( YEAR ( 'Table'[INIZIO] ), MONTH ( 'Table'[INIZIO] ), DAY ( 'Table'[INIZIO] ) ) = 'Table'[Date]
&& HOUR ( 'Table'[INIZIO] ) = 'Table'[Hour]
)
),
"Nr contenitori", CALCULATE (
COUNT ( 'Table'[CONTENITORE] ),
FILTER (
( 'Table' ),
DATE ( YEAR ( 'Table'[INIZIO] ), MONTH ( 'Table'[INIZIO] ), DAY ( 'Table'[INIZIO] ) ) = 'Table'[Date]
&& HOUR ( 'Table'[INIZIO] ) = 'Table'[Hour]
)
)
)
Best Regards
Rena
@Anonymous , Try like this
New column
Date =Table[INIZIO].date
hour =hour(Table[INIZIO])
New Table
summarize(Table, Table[Date], Table[hour], "op nr", distinctcount(Table[MATRICOLA]), "contenitori",count(Table[CONTENITORE]))
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |