Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter data per day in the report

Hello,   I have generated data for 150 employees for the last 5 years. In my SQL table. Currently, I have data like this in my table   Employee Id Employment start date Employment end date ...
  • Icey's avatar
    Icey
    6 years ago

    Hi Anonymous ,

     

    I don’t think you need to create another measure. Just remove "Trend data" column from your table visual.

     

     

    Best Regards,

    Icey

     

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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Anonymous - Sorry, I'm not quite following. Maybe use a MAX or MIN aggregation? What would be an expected result from the sample data you provided?

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Greg,

 

The data is just an example. I have a lot of data around 6 Million.

I have created a filter on TrendDate in the report and created a measure like this to calculate the sum of FTE based on date selection on date slicer.

 

SumFTE= CALCULATE(SUM(Employee_WorkerTrend[FULLTIMEEQUIVALENCY]),
FILTER(Employee_WorkerTrend,
Employee_WorkerTrend[TRENDDATE]<=MIN(Employee_WorkerTrend[TRENDDATE]) &&
Employee_WorkerTrend[TRENDDATE]>=MAX(Employee_WorkerTrend[TRENDDATE])))

 

After using this measure in my report grid, I get below error.

The resultset of a query to an external data source has exceeded the maximum allowed size of '1000000' rows.

 

Is this a correct approach or should be managed in another way.

 

Note: I am using directQuery for this report.