Hi everyone, first time posting but have found lots of useful content using this forum so thank you to all the contributors!
I have a question that i'm pretty sure is not going ot be possible but thought i'd ask anyway!
I work at an Airport and have built some Security Area performance reports that allow users to see current queue times and equipment performance. I only need current days data so have a report and/or page level filter to display latest metrics, we have 8 years worth of data stored in SQL server so for performance reasons i use a relative filter for the current day only.
The issue i have is that we have a monthly allowance of service measures, on the report page i need to display the number of times we have exceeded our service measure in the current month. I can achive this by setting a relative date filter to the report and using a measure to count the number of times that target has not been achived. However, performance of the report then suffers as i have to set relative visual filters to 'This Day' for the rest of the items.
I know i could use a calculated column to count the number of occurances but within Direct Query i cannot use many of the functions needed to achieve this, using a measure means it is effected by the report level filter.
Basically i need a DAY level filter on the report but also need to be able to get a value returned from a Month range for one specific visual.
Are there any functions available in Calculate Columns whilst in Direct query that would help achieve this?
Thanks in advance.
Ps. i do not have access to the data source in SQL server, only read permissions to build reports in Power bi.
