Forum Discussion

LairdLight's avatar
LairdLight
Frequent Visitor
9 years ago
Solved

Error when trying to display measure

I'm having issues with a measure that I created. It will display correctly in a matrix visual but if I try to display it in any other visual I get the error:

"MdxScript(Model) (5, 3) Calculation error in measure 'Union'[PhaseLogic]: A table of multiple values was supplied where a single value was expected."

 

Thiis is the measure:

PhaseLogic = IF(
VALUES('Union'[SourceAppGroupPhase]) < values('Union'[DestinationAppGroupPhase]),
MIN('Union'[SourceAppGroupPhase]),
MIN('Union'[DestinationAppGroupPhase])
)

 

I really just want the lower of two values contained in different fields of the same row:

SourceAppGroupPhaseDestinationAppGroupPhasePhaseLogic
121
211
232
121
311

 

The set of possible values for these fields is 1, 2, 3 or 4. I'm a novice at this so I'd appreciate any help diagnosing the error or designing an alternative approach.

 

Thanks

Need Help
  • LairdLight's avatar
    LairdLight
    9 years ago

    Thanks Anonymous

     

    That makes sense. It obviously wasn't the right approach. I've persisted in trying to fix it and seem to have got there with a nested IF function:

     

    PhaseLogic =

    if('Union'[SourceAppGroupPhase] = "1",
    1,
    if('Union'[DestinationAppGroupPhase] = "1",
    1,
    if('Union'[SourceAppGroupPhase] = "2",
    2,
    if('Union'[DestinationAppGroupPhase] = "2",
    2,
    if('Union'[SourceAppGroupPhase] = "3",
    3,
    if('Union'[DestinationAppGroupPhase] = "3",
    3,
    4)
    )))))

     

    Not sure that's its an elegant solution exactly but it seems to be working :)

     

    Thanks again for your response.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    I can see why this errors, you are asking the measure to check if an entire column is less than another entire column.  It probably works in the matrix because it is able to resolve when those columns are coming back as single values.

    How do you want this to work when your visualization has a selection that will return multiple rows?  How do you expect something like {1,2,2,1,3} < {2,1,3,2,1} to return a result?


    My gut instinct is that "VALUES('Union'[SourceAppGroupPhase]) < values('Union'[DestinationAppGroupPhase])," needs review.

    • LairdLight's avatar
      LairdLight
      Frequent Visitor
      9 years ago

      Thanks Anonymous

       

      That makes sense. It obviously wasn't the right approach. I've persisted in trying to fix it and seem to have got there with a nested IF function:

       

      PhaseLogic =

      if('Union'[SourceAppGroupPhase] = "1",
      1,
      if('Union'[DestinationAppGroupPhase] = "1",
      1,
      if('Union'[SourceAppGroupPhase] = "2",
      2,
      if('Union'[DestinationAppGroupPhase] = "2",
      2,
      if('Union'[SourceAppGroupPhase] = "3",
      3,
      if('Union'[DestinationAppGroupPhase] = "3",
      3,
      4)
      )))))

       

      Not sure that's its an elegant solution exactly but it seems to be working :)

       

      Thanks again for your response.