Forum Discussion

runnerpaul's avatar
runnerpaul
Regular Visitor
10 years ago

Use different filter calculations depending on comapny

Hi,

 

I have the below DAX which returns a budget calc for the current period

 

BudgetCalcForPeriod:=CALCULATE(SUM(Budget[Budget]), FILTER(Budget, Budget[YearPeriod] = MAX(BI_SalesAnalysis[YearPeriod])), FILTER( ALL( Budget[Product Group] ), 
    CONTAINS( VALUES( BI_SalesAnalysis[Product Group] ), BI_SalesAnalysis[Product Group], Budget[Product Group] ) ), FILTER( ALL( Budget[Period] ), 
    CONTAINS( VALUES( BI_SalesAnalysis[Fin Period] ), BI_SalesAnalysis[Fin Period], Budget[Period])), FILTER( ALL( Budget[Company] ), 
    CONTAINS( VALUES( BI_SalesAnalysis[Company] ), BI_SalesAnalysis[Company], Budget[Company] ) ), FILTER( ALL( Budget[Region] ), 
    CONTAINS( VALUES( BI_SalesAnalysis[Region] ), BI_SalesAnalysis[Region], Budget[Region] ) ), FILTER( ALL( Budget[Item Group] ), 
    CONTAINS( VALUES( BI_SalesAnalysis[Item Group] ), BI_SalesAnalysis[Item Group], Budget[Item Group] ) ))	

I want to edit it so that if a company field is populated with "agl" the budget is calculated as above but if it's "agp I want the same budget calculation minus this filter:

FILTER( ALL( Budget[Item Group] ), 
    CONTAINS( VALUES( BI_SalesAnalysis[Item Group] ), BI_SalesAnalysis[Item Group], Budget[Item Group] ) ))	

Is this possible and if so how do I do it?

 

Thanks

Paul

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    I'd have to see the original data to be sure, but it should theorectically be possible by using IF.