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. 
Jeanxyz's avatar
Jeanxyz
Power Participant
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! 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!