Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filter value based on multiple criteria

 

Hi,

Can you please help me filter value of the last year for each company in the table below?

The result in the last column should be 4 for company A, 200 for company C, and 4 for company B.

I tried to use lookupvalue, filter, and max but I couldn't figureout the arrangement in the formula.

YearValueCompanyValue in the final year
201910A 
20204A 
20215A 
20224A 
20198C 
20209C 
20217C 
2022200C 
201910B 
20204B 
20215B 
20224B 

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    Measure way:

    Measure = 
VAR maxyear =
    CALCULATE ( MAX ( 'Table'[Year] ), ALLEXCEPT ( 'Table', 'Table'[Company] ) )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Company] ), 'Table'[Year] = maxyear )
    )

     

    Alternatively,to use a calculated column as below.

    in the final year = 
VAR maxyear =
    CALCULATE (
        MAX ( 'Table'[Year] ),
        FILTER ( 'Table', 'Table'[Company] = EARLIER ( 'Table'[Company] ) )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Year] = maxyear
                && 'Table'[Company] = EARLIER ( 'Table'[Company] )
        )
    )

     

    Pbix as attached.

     

7 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Measure way:

    Measure = 
VAR maxyear =
    CALCULATE ( MAX ( 'Table'[Year] ), ALLEXCEPT ( 'Table', 'Table'[Company] ) )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Company] ), 'Table'[Year] = maxyear )
    )

     

    Alternatively,to use a calculated column as below.

    in the final year = 
VAR maxyear =
    CALCULATE (
        MAX ( 'Table'[Year] ),
        FILTER ( 'Table', 'Table'[Company] = EARLIER ( 'Table'[Company] ) )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Year] = maxyear
                && 'Table'[Company] = EARLIER ( 'Table'[Company] )
        )
    )

     

    Pbix as attached.

     

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Anonymous 

    try a measure

     

    Value in the final year= 
var _lastYear = calculate(max('Table'[Year]),ALLEXCEPT('Table','Table'[Company]))

RETURN
LOOKUPVALUE('Table'[Value], 'Table'[Company],SELECTEDVALUE('Table'[Company]), 'Table'[Year],_lastYear)

     

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Hi,

      It gave me an Error in the SelectedValue portion. Is there a way we can fix it?
      • az38's avatar
        az38
        Community Champion
        6 years ago

        Sorry Anonymous 

        it must be comma

        Value in the final year= 
var _lastYear = calculate(max('Table'[Year]),ALLEXCEPT('Table','Table'[Company]))

RETURN
LOOKUPVALUE('Table'[Value], 'Table'[Company],SELECTEDVALUE('Table'[Company]), 'Table'[Year],_lastYear)

         

        do not hesitate to give a kudo to useful posts and mark solutions as solution