Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Optimizing a measure filter with calculated table

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 CodeFinancial Year(Date)Account Head(GL Master)YTD Amount - 1YTD Amount - 2
111FY21Assets4509817901180025.73
111FY21Equity-12495100815180025.73
111FY21Expenses1823651648180025.73
111FY21Income-2602949869180025.73
111FY21Liability8547147510180025.73
111FY21Memo231583709.2180025.73
111FY21Other Comprehensive Income 180025.73
111FY21Tax expense-13970058.9180025.73
111FY21(blank) 180025.73
 FY21 Total 180025.73180025.73
111 Total  180025.73180025.73
Grand Total  180025.73180025.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.

 

 

 

 

Calculatetable filter

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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.