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
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!

     

     

     

     

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

      Hi angelobrg,

      Please take a try to add the distinct within FIRSTDATE and LASTDATE function.

       

      FIRSTDATE(DISTINCT('glpi_tickettasks glpi' [date]))

      LASTDATE(DISTINCT('glpi_tickettasks glpi' [date]))

       

      See if writing in this way would solve the issue. If not, then please share more details about the data model.

       

      Regards