topic Hourly Production by machine - Paginated Report in DAX Commands and Tips

Hourly Production by machine - Paginated Report

Paulo_IntelFab — Tue, 01 Nov 2022 13:10:35 GMT

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.

Re: Hourly Production by machine - Paginated Report

Anonymous — Mon, 07 Nov 2022 00:40:52 GMT

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-paginated-reports/

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

https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/tips-and-tricks-with-dax-table-functions-in-paginated-reports/

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.

Re: Hourly Production by machine - Paginated Report

Paulo_IntelFab — Sat, 05 Nov 2022 14:59:52 GMT

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.