Filter data per day in the report

[Anonymous]

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	Trend data	Salary	FTE
1	01/01/2015	01/01/2020	01/01/2015	1000,00	0,60
1	01/01/2015	01/01/2020	Continue till 01/01/2020	2000,00	0,80
1	01/01/2015	01/01/2020	01/01/2020	3000,00	1,00
2	01/01/2015	01/01/2020	01/01/2015	1000,00	0,60
2	01/01/2015	01/01/2020	Continue till 01/01/2020	2000,00	0,80
2	01/01/2015	01/01/2020	01/01/2020	3000,00	1,00

I don't want to show all data in the report per day but I want to show only 1 line per employee in the report. I have to show filter on Trend date and Once the user selects Trend date then data should be SUM like Salary or FTE.

Do I need to create a measure for Salary and FTE based on SUM? If so then Do I need to include TrendDate field on my grid?

[Icey · Accepted solution]

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.

[Anonymous]

maybe I didn't understand what the real need is, but part of the problem, at least, can be easily dealt with in power query with the table.group function

[Anonymous]

Hi Rocco,

Thank you for your reply. I can do that but I have a date range. When a user selects a specific date then It should sum the data based on the selected date. I have around or less than 1 Million data for a direct queries. 

[Anonymous]

hi @Anonymous ,

then you want a function that has in input an ID and a data range i.e. {data1,data2}   and should output the salary sum (FTE sum) for that ID for days from data1 to data2?

[Greg_Deckler]

@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]

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.