Forum Discussion

angelobrg's avatar
angelobrg
Regular Visitor
10 years ago

Filter data in the report period

Hello! I'm starting at power bi and I'm certain difficulty to generate the following report: I need the sum of a specific field values (in seconds) of a given id (id field value 1) but requires...
Need Help
KGrice's avatar
KGrice
Icon for Memorable Member rankMemorable Member
10 years ago

Hi angelobrg. If I understand correctly, you want to sum the actiontime field, which is recorded in seconds, and then convert that to hours. To do that, you can create a new measure that adds the seconds and divides the total by 3600 (divide seconds by 60 to get minutes, then by 60 again to get hours). Format in the Modeling tab to show however many decimal places you prefer, and give it a name that makes sense to you.

 

actiontime in hours = SUM(TableName[actiontime]) / 3600

 

You also want to calculate that specifically for id=1. You can create a second measure that builds on the first:

 

actiontime in hours ID1 = CALCULATE([actiontime in hours], TableName[id]=1)

 

You'll also need to format that measure, as the formatting is not inherited from previous measures.

 

This measure will also take into account the row context of any way you divide up your data, e.g., by month in a table.

angelobrg's avatar
angelobrg
Regular Visitor
10 years ago

the measure to bring the number of hours worked

but on the table it always returns the same value for every month

 

  • angelobrg's avatar
    angelobrg
    Regular Visitor
    10 years ago

    Hi,

     

    when I drag this measure to the table, the table is all empty.What can it be?

     

     

    Thanks.

     

    • v-micsh-msft's avatar
      v-micsh-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago

      Hi angelobrg,

      Check to see if the following formula would meet your requirements:

      Under Power BI Desktop data view, click the new measure icon, then input the following formula:

      AcHours := Calculate(
                Sum('table'[ActionTime])/3600, 
                Filter('table', 
                       And(FIRSTDATE('table'[date])<='table'[date],
                           And('table'[date]<=LASTDATE('table'[date]),
                               'table'[ID])
                           )
                       )
                  )

       

      The above formula would calculate the sum of the values between the time range in the current context, and then we need to build another slicer to filter the ID.

      See the result:

      If you only need the data from ID = 1, replace ‘table’[ID] with ‘table’[ID]=1.

      By the way, which measure that you put into the table show no values? If there is no filter, the measure should return the total value.

      If any further help needed, please feel free to post back.

      Regards

       

      • angelobrg's avatar
        angelobrg
        Regular Visitor
        10 years ago

        Thanks for your attention Michael,

        I created the suggested action, it does not show me any syntax error. But when I put the measure on the table is the error attached stating that a date column with duplicate values was specified in the call to the FIRSTDATE function.

        Follows the measure:

        Hours by type of care = Calculate (sum ('glpi_tickettasks glpi' [actiontime]) / 3600; filter ('glpi_tickettasks glpi'; AND (firstdate ('glpi_tickettasks glpi' [date]) <= 'glpi glpi_tickettasks' [date]; AND ('glpi_tickettasks glpi' [date] <= LASTDATE ('glpi_tickettasks glpi' [date]); 'glpi glpi_tickettasks' [taskcategories_id]))))

        Thanks for your help!