Need suggestion on filters or perhaps parameters

[VladyOselsky]

Hello,

This is not a question with one given answer, more a request for direction. Looking for suggestion on how to best setup dashboard that has the following requirements.

Client needs to be able to select customer and month year. Dashboards needs to limit all pages based on that selection. Some pages some only current month worth of data, however other pages compare current month versus 12 month going back from selected month. I looked into cross page filters and while it is exactly what I need to be able to show selected month I'm struggling to conceptualize how to aggregate 12 months of related information.  Currently I'm solving it by aggregating data on SQL side and brining back 2 rows for every element i need, 1 row is current month, the other row is 12 month aggregated. 

Second challenge that I'm facing a set of data that needs to be displayed on report is aggregate of multiple customers related to selected customer but their details need to be hidden and only show aggregate information for comparison and no detail on given customer. Since I'm doing the work on SQL I can search for all related customers and aggregate data before returning therefore preventing all details from ever being loaded into report.

Finally the real question do I try to bring in as much as data into Power BI and then filter it or do I setup parameters that just passed to stored procedures I already setup? My goal is not only to make best experience navigated the report but also keeping performance and required security in place. I know this might be confusion question but appriciate all suggetions.

[Anonymous · Accepted solution]

Hi @VladyOselsky,

#1, In fact, power bi visauls will auto aggregate value fields based on category/group field. You can add a column to check and remark records with specific groups and use this field and value fields to design visual, power bi will aggregate them based on category field.

For selector effect from filter, you can create a unconnected table as source of slicer, and use Dax formula to check it selections to use in measure formulas.

Using the SELECTEDVALUE function in DAX - SQLBI

#2, I'd like to suggest you design a report page with detail level information and configure it to invisible, and setting a drill through filter based on the main table field to navigate between main page and detial pages.

Set up drillthrough in Power BI reports - Power BI | Microsoft Learn

#3, You can create a query parameter filter to use in your data connector SQL statements and it should help to reduce the result table records amounts to help improve the performance.

For security part, you can try to enable RLS on your report based on current username and it will filter records based on current users if you assign role to them.

DAX USERPRINCIPALNAME - Use in RLS - Power BI Docs

Row-level security (RLS) with Power BI - Power BI | Microsoft Learn

Regards,

Xiaoxin Sheng