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

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