Find Value from Column based on Two Other Columns

[patri0t82]

Hello,

I need to create a measure that will find the value from 

'Targets - LTIR (Tabular)'[Value]

based on the selected date from

'Targets - LTIR (Tabular)'[Month / Year]

and 

the selected operating area from

'Targets - LTIR (Tabular)'[Operating Area]

The best I could come up with is

LTIR Target NEW =
CALCULATE (
VALUES ( 'Targets - LTIR (Tabular)'[Value] ),
FILTER ( ALL ( 'Targets - LTIR (Tabular)' ), 'Targets - LTIR (Tabular)'[Month / Year] = MAX ( 'Targets - LTIR (Tabular)'[Month / Year] ) ),
FILTER ( ALL ( 'Targets - LTIR (Tabular)' ), 'Targets - LTIR (Tabular)'[Operating Area] = MAX ( 'Targets - LTIR (Tabular)'[Operating Area] ) )

This formula appears to be ignoring the Operating Area filter though. Any help is greatly appreciated.

[Jeanxyz · Accepted solution]

It looks if you use values(), you can only reference a column or a table,  a table expression such as filter() is not allowed. By the way, the output of values() can be a list or scalar data. Are you expecting scalar data? If that's the case, you can try the measure below. 

LTIR Target NEW2 = CALCULATE(VALUES('Targets - LTIR (Tabular)'[Value]), FILTER(ALL('Targets - LTIR (Tabular)'),

'Targets - LTIR (Tabular)'[Month / Year] = MAX ( 'Targets - LTIR (Tabular)'[Month / Year] )),ALLEXCEPT( 'Targets - LTIR (Tabular)','Targets - LTIR (Tabular)'[Operating Area]))

If you are expecting a list from values, you might try a turnaround like the one below

LTIR Target NEW Tab =

DISTINCT(FILTER(ALL('Targets - LTIR (Tabular)'),

'Targets - LTIR (Tabular)'[Month / Year] = MAX ( 'Targets - LTIR (Tabular)'[Month / Year] ) && 'Targets - LTIR (Tabular)'[Operating Area]=MAX('Targets - LTIR (Tabular)'[Operating Area])))

I have created a test file. Here is the link. 

https://www.dropbox.com/s/0hdzjqy0k9pj14z/test%20file.pbix?dl=0

[patri0t82]

Thank you so much! Your file really helped. This is the formula I ended up using:

LTIR Target NEW =

CALCULATE (

MAX ( 'Targets - LTIR (Tabular)'[Value] ),

FILTER (

ALL ( 'Targets - LTIR (Tabular)' ),

'Targets - LTIR (Tabular)'[Month / Year]

= MAX ( 'Targets - LTIR (Tabular)'[Month / Year] )

),

FILTER (

ALL ( 'Targets - LTIR (Tabular)' ),

'Targets - LTIR (Tabular)'[Operating Area]

= MAX ( 'Targets - LTIR (Tabular)'[Operating Area] )

)

)

If I could ask just one more thing.. If I have no Operating Area selected I need it to represent 'CompanyX'.

For more context; our company (CompanyX) is made up of several Operating Areas. If no operating area is selected, it needs to look up CompanyX from the table. 

Thank you again so much!

[Jeanxyz]

Do you have more than one Operating Area selected? In your measure, if you select more than one Operating Area, only largest one is considered as a filter condition. Try if the one below meets your need

LTIR Target NEW =
CALCULATE (
VALUES ( 'Targets - LTIR (Tabular)'[Value] ),
FILTER ( ALL ( 'Targets - LTIR (Tabular)' ), 'Targets - LTIR (Tabular)'[Month / Year] = MAX ( 'Targets - LTIR (Tabular)'[Month / Year] ) ),
ALLEXCEPT( 'Targets - LTIR (Tabular)' ), 'Targets - LTIR (Tabular)'[Operating Area])

)

[patri0t82]

Thank you so much for the response. Yes I had one Operating Area selected

I tried your formula, though there appears to be an extra ) after the first Tabular in your ALLEXCEPT row.

This error is returned in my card

MdxScript(Model) (416, 1) Calculation error in measure 'Calculations'[LTIR Target NEW]: A table of multiple values was supplied where a single value was expected.

[patri0t82]

Hi again, I just wanted to follow up. I think I've got the final solution. It's a bit beastly but it's working well. There was more than CompanyX. It was also CompanyX1, X2, X3. Here's the final code - and thank you again so much!

LTIR Target =

IF (

SELECTEDVALUE ( 'Operating Areas and Value Centres'[Value Centre] ) <> "CompanyX1" &&

SELECTEDVALUE ( 'Operating Areas and Value Centres'[Value Centre] ) <> "CompanyX2" &&

SELECTEDVALUE ( 'Operating Areas and Value Centres'[Value Centre] ) <> "CompanyX3"

,

CALCULATE (

MAX ( 'Targets - LTIR (Tabular)'[Value] ),

FILTER (

ALL ( 'Targets - LTIR (Tabular)' ),

'Targets - LTIR (Tabular)'[Month / Year]

= MAX ( 'Targets - LTIR (Tabular)'[Month / Year] )

),

FILTER (

'Operating Areas and Value Centres',

'Operating Areas and Value Centres'[Value Centre] = "CompanyX"

)

),

IF (

SELECTEDVALUE ( 'Operating Areas and Value Centres'[Operating Area] ) = "",

CALCULATE (

MAX ( 'Targets - LTIR (Tabular)'[Value] ),

FILTER (

ALL ( 'Targets - LTIR (Tabular)' ),

'Targets - LTIR (Tabular)'[Month / Year]

= MAX ( 'Targets - LTIR (Tabular)'[Month / Year] )

),

FILTER (

ALL ( 'Targets - LTIR (Tabular)' ),

'Targets - LTIR (Tabular)'[Operating Area]

= SELECTEDVALUE ( 'Operating Areas and Value Centres'[Value Centre] )

)

),

CALCULATE (

MAX ( 'Targets - LTIR (Tabular)'[Value] ),

FILTER (

ALL ( 'Targets - LTIR (Tabular)' ),

'Targets - LTIR (Tabular)'[Month / Year]

= MAX ( 'Targets - LTIR (Tabular)'[Month / Year] )

),

FILTER (

ALL ( 'Targets - LTIR (Tabular)' ),

'Targets - LTIR (Tabular)'[Operating Area]

= MAX ( 'Targets - LTIR (Tabular)'[Operating Area] )

)

)))