Hourly Production by machine - Paginated Report

[Paulo_IntelFab]

Hello to all.

I'm a newcomer to DAX and started with a problem of converting an old PDF report created in C# to paginated report on Power BI.

The data I have is similar to this:

Machine | ProductionTimestamp | ProductionHour
-----------------------------------------------------
M1         | 01/01/2022 06:35:12    |  06
M1         | 01/01/2022 09:30:12    | 09
M1         | 01/01/2022 22:55:12    | 22
M2         | 01/01/2022 09:55:12    | 09
M2         | 01/01/2022 22:35:12    | 22
M3         | 01/01/2022 07:35:12    | 07
M3         | 01/01/2022 08:35:12    | 08

I need generate a daily paginated report with this information: hourly production by machine (in the TODAY()-1 date), between 04h and 0h.
Production is the count of ProductionTimestamp in each hour of day.

ProductionHour | M1 | M2 | M3
04                       |  0   |   0  | 0
05                       |  0   |   0  | 0
06                       |  1   |   0  | 0
07                       |  0   |   0  | 1
08                       |  0   |   0  | 1
09                       |  1   |   1  | 0
10                       |  0   |   0  | 0
...
22                       |  1   |   1  | 0
23                       |  0   |   0  | 0

To the moment, I have the hourly production for 1 machine, but I don't know how to go ahead and add a column per machine (in the same query).

My present code:
EVALUATE
SUMMARIZECOLUMNS (
'FACT-ProducaoBanburys'[HoraProducao],
'FACT-ProducaoBanburys'[Maquina_ID],
FILTER (
VALUES ( 'FACT-ProducaoBanburys'[DataProducao] ),
'FACT-ProducaoBanburys'[DataProducao]
= TODAY () - 1
),
FILTER (
VALUES ( 'FACT-ProducaoBanburys'[Maquina_ID] ),
'FACT-ProducaoBanburys'[Maquina_ID] = 1
),
"M1", COUNT ( 'FACT-ProducaoBanburys'[Data] )
)
ORDER BY 'FACT-ProducaoBanburys'[HoraProducao]

And the result is:
HoraProducao | M1
6 | 4
7 | 12
8 | 16
9 | 12
10 | 24
...

Thanks for any help. 

[Anonymous]

Hi  @Paulo_IntelFab ,

You can try the following dax:

DEFINE
    VAR ExceptionList =
        SUMMARIZECOLUMNS (
            'FACT-ProducaoBanburys'[HoraProducao],
            'FACT-ProducaoBanburys'[Maquina_ID],
            FILTER (
                VALUES ( 'FACT-ProducaoBanburys'[DataProducao] ),
                'FACT-ProducaoBanburys'[DataProducao]
                    = TODAY () - 1
            ),
            FILTER (
                VALUES ( 'FACT-ProducaoBanburys'[Maquina_ID] ),
                'FACT-ProducaoBanburys'[Maquina_ID] = 1
            )
        )
    VAR FullList =
        SUMMARIZECOLUMNS (
            'FACT-ProducaoBanburys'[HoraProducao],
            "M1", COUNT ( 'FACT-ProducaoBanburys'[Data] )
        )
EVALUATE
EXCEPT ( FullList, ExceptionList )

This is the dax related content of the paginated report, you can view this content:

https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/introduction-to-dax-for-pagi...

https://learn.microsoft.com/en-us/power-bi/paginated-reports/report-builder-expression-examples#Deci...

https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/tips-and-tricks-with-dax-tab...

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Paulo_IntelFab · Accepted solution]

Liu Yang, I appreciate your help. 
But, after a few try-and-errors, I found a solution for this problem. 
What I made was:
    - I create measures with the Count of all production filter by date and by machine (using CALCULATE)
    - Then I create a table using SELECTCOLUMNS with HourOfProduction as reference and the measures created previously as the columns. 

    It worked perfectly for the case and I replicated this  solution to other tables I needed in the report.  
    Thanks for your help.