CODEWITHSUNDEEP
powerbidax
2020db9
2020db9

Reputation: 307

SUMX Multiple Filter Criteria + VARs

I've seen a couple posts that have helped push me along with the proper DAX structure. My background is SQL so I would have approached this with a over partition, however, I think I'm pretty close with my current code. EARLIER() didn't seem to allow me to select my desired variables, so I saw another post that said to declare the variables within the calculated measure.

However, I can resolve my "syntax for 'FILTER' is incorrect".

Factor2 = SUMX(Test_Table,
    VAR FiscalYear = Test_Table[FiscalYear]
    VAR Segment    = Test_Table[Segment]
    Var PerChange  = FILTER(Test_Table,Test_Table[FiscalYear]=FiscalYear && Test_Table[Segment]=Segment),Test_Table[PercentChange])

RETURN PerChange/[MaxYear]

I was also unable to figure out how to apply an if/then with logic like:

IF FiscalYear = MAX(Year), then Factor2 = 1, ELSE: (run above code)

If this fairly simple to include, please advise, otherwise I can continue to troubleshoot.

Upvotes: 0

Views: 925

Answers (1)

Ozan Sen
Ozan Sen

Reputation: 2615

Try this one:

Factor2 =
VAR FiscalYear = Test_Table[FiscalYear]
VAR Segment = Test_Table[Segment]
VAR PerChange =
    FILTER (
        Test_Table,
        Test_Table[FiscalYear] = FiscalYear
            && Test_Table[Segment] = Segment
    )
VAR Result =
    SUMX ( PerChange, Test_Table[PercentChange] )
RETURN
    CALCULATE (
        IF ( FiscalYear = MAX ( Test_Table[Year] ), 1, DIVIDE ( Result, [MaxYear] ) )
    )

Your comment "What if we just were to try and find the PercentChange for max(FiscalYear) by segment/PercentChange Would that be eaiser?" So I tried this one:

Factor3 =
VAR FiscalYear =
    MAX ( Test_Table[FiscalYear] ) -- VAR Segment = Test_Table[Segment]
VAR PerChange =
    FILTER (
        Test_Table,
        Test_Table[FiscalYear] = FiscalYear -- && Test_Table[Segment] = Segment
    )
VAR Result =
    SUMX ( PerChange, Test_Table[PercentChange] )
RETURN
    Result

Note: "--" sign preceding the statement means that line is converted to comments, and has no effect on DAX code.

Upvotes: 1

Related Questions