Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DDL1976
Frequent Visitor

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!
1 ACCEPTED SOLUTION
AntrikshSharma
Community Champion
Community Champion

@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

 

View solution in original post

2 REPLIES 2
DDL1976
Frequent Visitor

Thats amazing thank you!

AntrikshSharma
Community Champion
Community Champion

@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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors