Forum Discussion
Solved
DAX Filter Problem
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!
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 ResultAnd 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
2 Replies
Thats amazing thank you!
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 ResultAnd 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
