powerbidaxdaxstudiopowerbi-paginated-reports

DAX - Production by Hour and By Machine

I'm new to DAX and started with a problem of converting an old PDF report created in C# to paginated report on 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, 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    |    8
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.

Thanks for any help.

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
 ...
Solution

  • You can start by creating a hour table using create new table:

    Hour = SELECTCOLUMNS(UNION(GENERATESERIES(04,23,01),{00}),"Hour",[Value])

    Your resulting table:

    HTable

    Then create a one-many relationship with your fact table, like this on the model view:

    Model View

    Now Come to your main screen, and write this dax codes for each and every one of your Machine Code:

    For M1 Machine

    M1 = CALCULATE(
                COUNT(YourTbl[ProductionTimestamp]),YourTbl[Machine]="M1")

    For M2 Machine

    M2 = CALCULATE(
                COUNT(YourTbl[ProductionTimestamp]),YourTbl[Machine]="M2")

    For M3 Machine

    M3 = CALCULATE(
                COUNT(YourTbl[ProductionTimestamp]),YourTbl[Machine]="M3")

    To finish the task, create a table visual:

    Put [Hour] Column on your created hour table into row, and above measures next to values area: The result:

    FDS