In my PBX I have a measure created that tracks the 3 month rolling average number of views. Whatever month you are on it will average the 3 prior months.
I’m trying to create a new filter/measure that allows me to filter out reports that are determined to be “Non Active” reports.
Non Active threshold can be different for each team. I’m trying to add a slicer/parameter to the report based off the 3 month rolling average number. Give the user the option to move the slider so that it removes reports who’s 3 month average is below the slider number.
For this data set, I’ve got three reports and if i move the slider to 50 then one of the reports should drop off because their 3 month rolling average is below 50.
I’m not sure exactly how to get this process started? Any help would be appreciated.
I’m thinking I need a parameter based on the 3 month rolling average. However, that would then just affect the visual I add it to. I want to filter out any report that does not meet the sliders threshold. Should it be a calculated column? DEV - Random Dates Shared.pbix (99.5 KB) Random Report View Dates.xlsx (35.9 KB)
Hi @chad.sharpe - Based on the requirement, don’t think it is straightforward as filtering needs to be dynamic and in Power BI we can’t use measures as page/report filter Also, Calculated Columns can’t be dynamic so can’t be used.
Two options,
Power Query/Data Source – If starting month for rolling average is fixed then try to get it done inside Power Query/Source . Setup a Power Query parameter and user can provide input to parameter through filter. Based on provided input and rolling average get only required data from Source or apply logic in Power Query.
Limitations - Need Direct query, fixed dates and knowledge of Power Query/Source changes to calculate Rolling average inside it.
Second Option (DAX) - To create measures based on selected value and Rolling average. Filter Reports in visuals that meet the criteria using measures. Main Drawback with this is that different filter have different Context and same approach can’t be used across all. Few examples below.
For visuals, having Repot Name as context. Create a measure as below and use directly in the filter section.
Report Filter = var selectedv = SELECTEDVALUE(Parameter[Parameter])
var Reports = SUMMARIZE(filter(values(‘Audit Logs’[Report Name]),[3 Month Rolling Average] > selectedv),‘Audit Logs’[Report Name])
return
if(max(‘Audit Logs’[Report Name]) in Reports,1,0)
For visuals, that don’t have Report Name context like in Card visual. Need to modify individual measures to filter out Reports as below.
Report View New = sumx(SUMMARIZE(filter(values('Audit Logs'[Report Name]),[3 Month Rolling Average] > SELECTEDVALUE(Parameter[Parameter])),"Report Views1",[Report Views]),[Report Views1])
Similarly for last visual, where there is additional filter context from “Month & Year”, different calculation is needed.