Forum Discussion

Shafi293's avatar
Shafi293
Frequent Visitor
7 years ago
Solved

How to optimize below DAX

Hi guys can u plz help me out with the  below query ,it takes around more than 50sec to show results 
w1 =
CALCULATE([SettlementAmountm],
Filter(Fact_Revenue_management,
if(ISBLANK(Fact_Revenue_management[BankTransactionDate].[Date] ), MAX(DimDate[Date]), Fact_Revenue_management[BankTransactionDate].[Date]) <= Fact_Revenue_management[PaymentDueDate].[Date]
&&
 Fact_Revenue_management
[ReconFlagForBank] = BLANK() || max(DimDate[Date]) < Fact_Revenue_management[BankTransactionDate].[Date]
&&
Fact_Revenue_management[DimPaymentGatewayID]<>21 && Fact_Revenue_management[DimPaymentGatewayID]<>14 ))

+
CALCULATE([Paypal Outstanding],Filter((Fact_Revenue_management),
if(ISBLANK(Fact_Revenue_management[BankTransactionDate].[Date] ), MAX(DimDate[Date]), Fact_Revenue_management[BankTransactionDate].[Date]) <= Fact_Revenue_management[PaymentDueDate].[Date] && ( Fact_Revenue_management
[ReconFlagForBank] = BLANK() || max(DimDate[Date]) < Fact_Revenue_management[BankTransactionDate].[Date]) && Fact_Revenue_management[DimPaymentGatewayID]=14 ))

other measures used are 
SettlementAmountm =
Calculate(Sum(Fact_Revenue_management[SettlementAmount]),Fact_Revenue_management[DimPaymentGatewayID] <>14,FIlter(DimPaymentGateway,DimPaymentGateway[TFPaymentID]="True" && DimPaymentGateway[DimPaymentGatewayID] <>21 && DimPaymentGateway[DimPaymentGatewayID] <>14))
+
[Paypal settlement]
+
Calculate(Sum(Fact_Revenue_management[SettlementAmount]),FIlter(DimPaymentGateway,DimPaymentGateway[TFPaymentID]="False" ))
+CALCULATE(Sum(Roku[TransactionAmount]),Filter(DimPaymentGateway,DimPaymentGateway[DimPaymentGatewayID]=21))