Optimizing a measure filter with calculated table

[Anonymous]

Hi,

I am trying to optimize my measure below by replacing the filter conditions with Calculatetable. I was able to replace my date filter. But replacing the other filter on GL Master is not giving desired results. GL Master has multiple hierarchy levels. Though it returns correct value at the summation level, but repeats the sum value when report is crossfiltered at any GL Master hierarchy:

YTD Amount - 1 =

var max_date = max('Date'[Date])

var BS = CALCULATE([Amount (period) New],CALCULATETABLE('Date',all('Date'[Date]),'Date'[Date] <= max_date),FILTER('Gl Master', left('GL Master'[Account Head],3) in {"Ass","Equ","Lia",BLANK()}))

var PnL = CALCULATE([Amount (period) New],DATESYTD('Date'[Date],"03/31"),FILTER('Gl Master',left('GL Master'[Account Head],3) in {"Inc","Exp","Tax","Oth","Mem","Exc"}))

return BS+PnL

YTD Amount - 2 =

var max_date = max('Date'[Date])

var BS = CALCULATE([Amount (period) New],CALCULATETABLE('Date',all('Date'[Date]),'Date'[Date] <= max_date),CALCULATETABLE('Gl Master', left('GL Master'[Account Head],3) in {"Ass","Equ","Lia",BLANK()}))

var PnL = CALCULATE([Amount (period) New],DATESYTD('Date'[Date],"03/31"),CALCULATETABLE('Gl Master',left('GL Master'[Account Head],3) in {"Inc","Exp","Tax","Oth","Mem","Exc"}))

return BS+PnL

Result:

Entity Code	Financial Year(Date)	Account Head(GL Master)	YTD Amount - 1	YTD Amount - 2
111	FY21	Assets	4509817901	180025.73
111	FY21	Equity	-12495100815	180025.73
111	FY21	Expenses	1823651648	180025.73
111	FY21	Income	-2602949869	180025.73
111	FY21	Liability	8547147510	180025.73
111	FY21	Memo	231583709.2	180025.73
111	FY21	Other Comprehensive Income		180025.73
111	FY21	Tax expense	-13970058.9	180025.73
111	FY21	(blank)		180025.73
	FY21 Total		180025.73	180025.73
111 Total			180025.73	180025.73
Grand Total			180025.73	180025.73

Is there any way to make Calculatetable work dynamically with report slicers or optimize this measure further with desired results (as in YTD Amount -1)?

FYI: I have tried ALLSELECTED two ways:

1) CALCULATETABLE(ALLSELECTED('Gl Master'), left('GL Master'[Account Head],3) in ....................

2) CALCULATETABLE('Gl Master',ALLSELECTED(), left('GL Master'[Account Head],3) in ....................

Thanks in advance,

Shailee.

[amitchandak]

@Anonymous , both filter, and calculatetable is going to generate a table as output that will filter. Unless calculatetable add a value I doubt there is need to use it.  Also you are using the calculate table on the master table that will be limited one table, create on fact that will allow you to filter all master table inside one calculatetable.

[Anonymous]

Hi Amit,

I was trying to replace Filter with Calculatetable for optimizing my measure basis the following article:

https://www.sqlbi.com/articles/filter-vs-calculatetable-optimization-using-cardinality-estimation/#

As my fact table is huge and this measure extrapolates it on my Date table for all dates within the selected range. Plus it does a lookup on my GL Master table which is also large compared to other masters.