Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Summarize grouping by 2 columns

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:

MODULOMENUCONTENITOREINIZIOMATRICOLA
Replenish.Reappro palette43600000001188974510 ago 20 06:05:40SHER
Replenish.Reappro palette43600000001178559710 ago 20 06:07:27SEIB
Replenish.Reappro palette43600000001071097210 ago 20 06:08:51SHER
Replenish.Reappro palette43600000001192045510 ago 20 06:11:07SEIB
Replenish.Reappro palette43600000001184373010 ago 20 06:11:33EMAT
Replenish.Reappro palette43600000001186797210 ago 20 06:12:35SHER
Replenish.Rgt pkg manuel43600000001181569010 ago 20 06:13:54SINN
Replenish.Reappro palette43600000000779544910 ago 20 07:15:16SEIB
Replenish.Rgt pkg manuel43600000001132901210 ago 20 07:14:06ASTL
Replenish.Reappro palette43600000001174992610 ago 20 07:15:23EMAT
Replenish.Reappro palette43600000001122009810 ago 20 07:17:28SEIB
Replenish.Reappro palette43600000001192388310 ago 20 07:20:01SHER
Replenish.Reappro palette43600000001191830810 ago 20 07:19:46EMAT
Replenish.Reappro palette43600000001179283010 ago 20 07:21:35SEIB
Replenish.Reappro palette43600000001191522210 ago 20 07:22:27ASTL
Replenish.Reappro palette43600000001150073210 ago 20 07:23:42EMAT
Replenish.Rgt pkg manuel43600000001152458510 ago 20 07:24:00EBOJ
Replenish.Reappro palette43600000001092563510 ago 20 07:27:03SEIB
Replenish.Rgt pkg manuel43600000001166301710 ago 20 07:28:24EBOJ
Replenish.Rgt pkg manuel43600000001184404110 ago 20 07:20:00MAMI
Replenish.Rgt pkg manuel43600000001180447210 ago 20 06:32:51DEKA
Replenish.Reappro palette43600000001184148410 ago 20 06:36:19ASTL
Replenish.Reappro palette43600000001072060510 ago 20 06:39:43SEIB
Replenish.Rgt pkg manuel43600000001102433711 ago 20 06:30:02EBOJ
Replenish.Reappro palette43600000001189999711 ago 20 06:44:18SEIB
Replenish.Reappro palette43600000001148320211 ago 20 06:41:31ASTL
Replenish.Reappro palette43600000001006405111 ago 20 06:45:07EMAT
Replenish.Rgt pkg manuel43600000001181030511 ago 20 06:46:14DEKA
Replenish.Reappro palette43600000001154860411 ago 20 06:46:35SHER
Replenish.Rgt pkg manuel43600000001114345811 ago 20 06:43:20EBOJ
Replenish.Rgt pkg manuel43600000001168308411 ago 20 06:50:13EBOJ
Replenish.Reappro palette43600000001184366211 ago 20 06:52:53EMAT
Replenish.Rgt pkg manuel43600000001178333311 ago 20 06:59:53ABMA
Replenish.Reappro palette43600000001068393111 ago 20 06:50:00SHER
Replenish.Reappro palette43600000001166222511 ago 20 06:59:11SEIB
Replenish.Reappro palette43600000000862672811 ago 20 06:58:13EMAT
Replenish.Rgt pkg manuel43600000001191462111 ago 20 06:59:55EBOJ
Replenish.Rgt pkg manuel43600000001179255711 ago 20 06:47:21DEKA
Replenish.Reappro palette43600000000896037211 ago 20 07:04:04SEIB
Replenish.Rgt pkg manuel43600000001177703511 ago 20 07:04:21EBOJ
Replenish.Rgt pkg manuel43600000001190976411 ago 20 07:05:17DEKA
Replenish.Rgt pkg manuel43600000001144297111 ago 20 07:13:18EBOJ
Replenish.Reappro palette43600000001140069811 ago 20 07:15:22ASTL
Replenish.Reappro palette43600000001187348511 ago 20 07:12:55SEIB

 

 

Here the output I'm looking for to get:

datehournr opnr  contenitori
10/08/20206610
10/08/20207613
11/08/20206718
11/08/2020746

 

Do you have any formulas to solve it?

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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]))

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

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]
        )
    )
)

Summarize grouping by 2 columns.JPG

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

@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]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.