topic Re: dax measure with ALL( DatesTable) does not ignore date filter in DAX Commands and Tips

dax measure with ALL( DatesTable) does not ignore date filter

Anonymous — Tue, 20 Dec 2022 15:27:03 GMT

Hi Folks,

I want to calculate:
AccountsReceivable = CALCULATE( SUMX( FACT_Actuals_Master, (FACT_Actuals_Master[Credits] - FACT_Actuals_Master[Debits]) )

I also need to add the filter FILTER( FACT_Actuals_Master, [Account Master Name] = "Accounts Receivable")

I also want to ignore any filtering coming from the relationship Datestable[DATE] = FACT_Actuals_Master[Date]

I have: AccountsReceivable = CALCULATE( SUMX( FACT_Actuals_Master, (FACT_Actuals_Master[Credits] - FACT_Actuals_Master[Debits]) ),
FILTER( FACT_Actuals_Master, [Account Master Name] = "Accounts Receivable"),
ALL(DatesTable) )

There is a DatesTable[MonthYear] in the filters pane acting on all pages, when it is used, the AccountsReceivable total changes, but is should remain constant.

Any thoughts/pointers?

Thanks

Re: dax measure with ALL( DatesTable) does not ignore date filter

Greg_Deckler — Tue, 20 Dec 2022 15:36:37 GMT

Anonymous It's hard to be certain what is going on here. Sometimes these kinds of issues are caused by the Auto-exist "feature".

Re: dax measure with ALL( DatesTable) does not ignore date filter

Anonymous — Tue, 20 Dec 2022 16:29:34 GMT

Greg_Deckler, thanks, I'll take a look at that and if I can't see anything obvious I'll set up a pbix replicating the problem.

Re: dax measure with ALL( DatesTable) does not ignore date filter

Anonymous — Tue, 20 Dec 2022 23:12:59 GMT

Greg_Deckler I think I've shared a sample database with you here: https://www.dropbox.com/s/lhmjui5bmkgsri2/AR%20Database.pbix?dl=0

New to this, may not have done this properly.

Re: dax measure with ALL( DatesTable) does not ignore date filter

ppm1 — Wed, 21 Dec 2022 02:56:45 GMT

Please try this version.

Converted_AR2 = VAR SelectedRate = SWITCH ( TRUE (), [SelectedCurrency] = "CAD", 1, [SelectedCurrency] = "EUR", P_EUR_to_CAD_Rate[EUR_to_CAD_Rate_Value], [SelectedCurrency] = "INR", DIVIDE ( 1, P_INR_to_CAD_Rate[INR_to_CAD_Rate_Value] ), [SelectedCurrency] = "USD", P_USD_to_CAD_Rate[USD_to_CAD_Rate_Value], BLANK () ) VAR ExchangeRate = IF ( MAX ( DatesTable[Date] ) > Labels[Ledger_LatestDate], SelectedRate, [Exchange_Rate_Normalised] ) VAR AccountsReceivable = CALCULATE ( SUMX ( FACT_Actuals_Master, ( FACT_Actuals_Master[Credits] - FACT_Actuals_Master[Debits] ) ), FACT_Actuals_Master[Account Master Name] = "Accounts Receivable", ALL ( DatesTable ) ) RETURN AccountsReceivable * ExchangeRate * -1

Pat

Re: dax measure with ALL( DatesTable) does not ignore date filter

Anonymous — Wed, 21 Dec 2022 14:39:53 GMT

ppm1 Thanks ppm1, that works perfectly for me.