Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vhoang148
Regular Visitor

Aggregates and Measures that uses filter

I'm rebuilding some reports to start using Aggregations, but I'm noticing odd behavior (or at least what I think it is odd); just wanted to see with the community if this is normal behavior or not.

 

So, I have three tables:

  1. Location
    1. Location ID (Primary key)
    2. Other columns are related to the location
  2. A custom Fiscal Calendar
    1. ID for each fiscal period (the periods are not standard length, starts on a different Sunday every fiscal year, and ends on a different Saturday) (set as Primay Key)
    2. the Fiscal year
    3. Fiscal period name (period names are the same each year)
  3. Sales
    1. Location ID (from Location)
    2. Fiscal period ID (from Fiscal Calendar)
    3. ID for each Item sold (there is a portion of the report that once drilled into a location, shows item level detail)
    4. Week Of Sales (always a Sunday)
    5. Other columns are sales data (i.e. total units, total ext retail, etc)

That's how things have been built and currently only import the last couple of fiscal years. Bringing in Aggregation to expand report of have multiple fiscal years (as I have been getting requests for the report with years that are no longer on the report). So, I built an aggregate table that summarize the sales information grouped by Location ID and Fiscal Period ID.

 

The aggregation works perfectly..until I start working with setting "default" values when no slicer selection is done for the fiscal year. The measure I wrote is as follows (simplified):

Total Revenue TY =
var AltYear = Lookup(FiscalCalendar[FiscalYear], FiscalCalendar[CurrentYear], True())
Return

If( HasOneValue(FiscalCalendar[FiscalYear])
    , sum( Sales[Revenue] )
    , Calculate( sum( Sales[Revenue] ), filter(FiscalCalendar, FiscalCalendar[FiscalYear] = AltYear)
)

When using slicers to select the Fiscal Year, (based on SQL Profiler), it goes to the Aggregate table, but when slicers are cleared, instead of using the Aggregate table, it goes to the DirectQuery. Is this normal when using Aggregations and then having a filter within the measure?

 

The LY function is even odder - it never hits the Aggregate table, just goes directly to the DirectQuery table. Here is the simplified measure:

Total Revenue LY =
var AltYear = SelectedValue(FiscalCalendar[FiscalYear], Lookup(FiscalCalendar[FiscalYear], FiscalCalendar[CurrentYear], True()))-1
Return

Calculate( sum( Sales[Revenue] ), filter(AllExcept(FiscalCalendar, FiscalCalendar[Name]), FiscalCalendar[FiscalYear] = AltYear)

Am I doing something wrong in the Aggregate setup or is the filter within the measure messing things up?

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @vhoang148 ,

I'm still a little confused about your scenario.

Which data source do you use and how do you connect it?

If it is convenient, could you share some data sample and the screenshot of the issues so that I could understand the scenario better?

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-piga-msft ,

 

I'm connecting to a MS SQL Server with the sales data table as Direct query and connecting to the other tables as Dual setting. I'll try to make a sample data this week; though, I think I figured out the issue after testing it out for a few hours on Friday having the formula look at just one period. Started with this (the var CurrentPeriod ends up being a value of 34):

 

 

Total Revenue =
var CurrentPeriod = Lookup(FiscalCalendar[PeriodID], FiscalCalendar[CurrentPeriod], True())
Return

Calculate(
    sum( Sales[Revenue )
    , Filter(
        Sales
        , Sales[PeriodID] = CurrentPeriod
    )
)

 

 

This triggered DirectQuery instead of calling against the Aggregate table. When I changed the code to:

 

Total Revenue =
Calculate(
    sum( Sales[Revenue )
    , Filter(
        Sales
        , Sales[PeriodID] = 34
    )
)

This did not trigger a DirectQuery, and instead used the Aggregate table. I tested this out a few other ways using variables and using nested calculations, and the only time it triggers the Aggregate table is when I use slicers or when I hard code the values. So, I determined that the way PBI is currrently programmed, it doesn't recognize variables as a valid "filter" that will trigger the use of the Aggregate tables and will use DirectQuery instead. I made a small work around in my both my sales table view and the aggregate table with certain flags; and those flags when used went to the aggregate table.

 

Thanks - V

 

@v-piga-msft, here is a simplified version of the report sample PBIX based on the current report (with the exception that I added an aggregate table); also within the PBIX is a version of the measure that I currently use in the current report (it allows for dynamic x-axis/row labels). Below is an image of how I have some of the relationships done.

 

I got the Aggregate table to trigger if I added a true/false column to both the sales and the aggregate table (not an ideal solution, but a solution). This solution so far works only when I'm looking at totals, but when I create a matrix, where it lists the period by row, it triggers Direct Query when it does not trigger DirectQuery if I use the slicer.

 

I tried to add an additional aggregate that roll up by the three IDs to do the dynamic labels, but it all triggers DirectQuery and I can't figure out why. It even triggers Direct if I make it roll up by location/calendar id, and ItemClassID from title.

 

(in this image, the expected table setups should have Location/FiscalCalendar/ItemClass as Dual, Sales and Item as DirectQuery, and any aggregate table and the unpivoted table as Import)

Relationships.jpg

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.