Does Power BI Support Aggregate Awareness?

[shaibu]

Does Power BI support aggregate awareness – Aggregate awareness is supported in OBIEE.

Scenario

Sales_Amt	Sales_Amt	Note
Fact_Sales_Day	Sales_Amt	Sum(Fact_sales_day.sales_amt) <> Fact.sales_month.sales_amt
Fact_Sales_Month	Sales_Amt	Sum(Fact_sales_month.sales_amt) <> Fact.sales_year.sales_amt
Fact_Sales_Year	Sales_Amt

Sales Report requirement

Drill Path	Expected Sales_amt
Year	One row per year & the sales amount = one row in Fact_sales_year
Year->Month	One row per month & the total sales amount for the year = aggregation of Fact_sales_month for the selected year
Year->Month->Day	One row per day & the total sales amount for the year = aggregation of Fact_sales_Day for the selected year

[Greg_Deckler]

Not sure if this is the right answer, but Power BI supports context when you use measures. So, you would create a single measure m_Sales_Amt which would have a formula such as SUM(Table[Sales_Amt). Technically, for something this basic, you wouldn't need to use a measure, you could use the default aggregations. Then, as you drill down through, the measure will reflect the current context of the visualization.

[shaibu]

Not sure if I've understood .  In my case , there are 3 physical fact tables (fact_sales_day ,fact_Sales_month,fact_Sales_year) & three physical columns (sls_amt_day in fact_day, sls_amt_mnth in fact_month ,sls_amt_year , fact_year) . I understand that in OBIEE I can create one logical measure sls_amt & map all the physical measures (sls_amt_day,sls_amt_mnth,sls_amt_year) to logical fact fact_Sales (logical fact sales is mapped to physical fact fact_year,month day). Depending on the drill path , aggregates from respective fact ie if year then fact_year, month then Fact_month  & day then fact_Day.

What is the equivalent in Power BI?

[v-chuncz-msft · Accepted solution]

@shaibu,

You may create a calendar table and use HASONEVALUE ( 'Calendar'[Date].[Day] ) in nested IF Function to add a measure.