Forum Discussion

patri0t82's avatar
patri0t82
Post Patron
4 years ago
Solved

Find Value from Column based on Two Other Columns

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 o...
Need Help
  • Jeanxyz's avatar
    Jeanxyz
    4 years ago

    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. 
patri0t82's avatar
patri0t82
Post Patron
4 years ago

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's avatar
patri0t82
Post Patron
4 years ago

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] )
)
)))