topic Re: DAX Filter Problem in DAX Commands and Tips

DAX Filter Problem

DDL1976 — Fri, 12 Mar 2021 20:57:24 GMT

I am new to the wqorld of DAX and Power BI...

I have created the following 2 measures but they dont work...

CFY = LOOKUPVALUE(DimPeriod[Financial Year], DimPeriod[Period End Date], LASTDATE(DimPeriod[Period End Date]))

AC = CALCULATE(SUM(FactTransactionLine[Value]), FILTER(DimPeriod, DimPeriod[Financial Year] = [CFY]))

I don't get an error however the result is the same as if there is no filter.

But this measure work perfectly

AC = CALCULATE(SUM(FactTransactionLine[Value]), FILTER(DimPeriod, DimPeriod[Financial Year] = "2020/21"))

I have checked that my CFY easure returns 2020/21 by adding it to a table visual...

Any help would be awesome!

Re: DAX Filter Problem

AntrikshSharma — Sat, 13 Mar 2021 02:38:23 GMT

DDL1976 The former isn't working due to a mechanism know as "Context Transition", pretty sophisticated thing but you will get a hang of it soon.

For now write your code like this:

AC = VAR CurrentFinancialYear = [CFY] VAR DateFilter = FILTER ( DimPeriod, DimPeriod[Financial Year] = CurrentFinancialYear ) VAR Result = CALCULATE ( SUM ( FactTransactionLine[Value] ), DateFilter ) RETURN Result

And once you are comfortable with DAX write it like this:

AC = VAR CurrentFinancialYear = [CFY] VAR Result = CALCULATE ( SUM ( FactTransactionLine[Value] ), KEEPFILTERS ( DimPeriod[Financial Year] = CurrentFinancialYear ) ) RETURN Result

Re: DAX Filter Problem

DDL1976 — Sat, 13 Mar 2021 09:12:15 GMT

Thats amazing thank you!