Forum Discussion

HussainWork's avatar
HussainWork
New Member
4 years ago

Help in Error: a table with multiple values

Greetings,

I'm trying to create a caluated column but getting the error message (A table of multiple values was supplied where a single value was expected.)

 

There are 4 tables in the model (listed at the end of the post; tags_information, tags_readings, acknowledgement_reading, & acknowledgement_status).

I'm trying to check to for 2 conditions for each value in the tags_reading table:

  1. If the tag meets a certin condition (Running or not running, or another associated value from another table (achknowledgement table) is higher than a specific value).
  2. If the tag is lower than a specific limit

I'm creating a calulated column part of the tags_reading table using mulitple variables & LookupValues. The DAX is below.

Trying to troubleshoot the issue, I trying returning each variable to check, I seems the variable (_ack) is returning the error message.

 

performanceLow_withAcknowledgement = 

var _limit = LOOKUPVALUE(tags_information[Performance Low Limit], tags_information[Tags], tags_readings[Tags])
var _ackTag = LOOKUPVALUE(tags_information[Acknowledgement Tags],tags_information[Tags], tags_readings[Tags])
var _ackType = LOOKUPVALUE(tags_information[Acknowledgement Type],tags_information[Tags], tags_readings[Tags])

var _ackReading =
    IF(
        _ackType = "Value",
        LOOKUPVALUE(
            acknowledgement_reading[Acknowledgement Readings],
            acknowledgement_reading[Acknowledgement Tags],_ackTag),
        IF(
            _ackType = "Status",
            LOOKUPVALUE(
                acknowledgement_status[Acknowledgement Status],
                acknowledgement_status[Acknowledgement Tags],_ackTag)
        )
    )

var _ackLimit = 
    IF(
        _ackType = "Value",
        LOOKUPVALUE(
            tags_information[Acknowledgement Value],
            tags_information[Tags],tags_readings[Tags]),
        IF(
            _ackType = "Status",
            LOOKUPVALUE(
                tags_information[Acknowledgement Value],
                tags_information[Tags],tags_readings[Tags])
        )
    )

var _ack =
    IF(
        _ackType = "Value",
        IF(_ackReading < _ackLimit, "Offline", "Online"),
        IF(
            _ackType = "Status",
            IF(_ackReading = _ackLimit, "Offline", "Online"),
            IF(
                _ackType="Always Online",
                "Online"
            )
        )
    )

RETURN

IF(
    _ack = "Online" && tags_readings[Reading] < _limit,
    "Performance Low"
)

 

 

Tables Relationships 

 

Each Tags_information[Tags] has only one Tags_information[Acknowledgement Tags]
The same [Acknowledgement Tags] might be associated with multiple [Tags]

 

Tags_information Table

TagsPerformance Low LimitAcknowledgement TagsAcknowlegement TypeAcknowlegement Limit
T1230A1Value30
T2260A1Value30
T3370A1Value30
T4510A2StatusNot Running
T5120A2StatusNot Running
T61500A2StatusNot Running

 

tags_Readings Table

Tags

Date & Time

(Hourly)

Reading
T11/1/2022 00:0050
T21/1/2022 00:0040
T31/1/2022 00:0020
T41/1/2022 00:00100
T11/1/2022 01:0010
T21/1/2022 01:0030

 

Acknowledgement_Reading Table

Acknowledgement Tags

Date & Time

(Hourly)

Achknowledgement Value
A11/1/2022 00:00160
A31/1/2022 00:004
A41/1/2022 00:0060
A11/1/2022 01:00100

 

Acknowledgement_status table

Acknowledgement Tags

Date & Time

(Hourly)

Acknowledgement Status
A21/1/2022 00:00Not Running
A51/1/2022 00:00Running
A21/1/2022 01:00Not Running
A51/1/2022 01:00Not Running

6 Replies

  • HussainWork , seem like one of the lookups is giving more than one value, while only one is expected. You can try like

     

    MAxx(filter(tags_information, tags_information[Tags] = tags_readings[Tags]),tags_information[Performance Low Limit])

    • HussainWork's avatar
      HussainWork
      New Member

      Thanks amitchandak , it cleared the error, but it returns the maximum valve in the table for that tag where it suppose to return the value at that specific hour (according for to [Date & Time] column value).

    • HussainWork's avatar
      HussainWork
      New Member

      Hi tamerj1 ,

      I'm creating it in the (tags_reading) table as I need to evaluate each reading raw value at that specific [date & time].

      • tamerj1's avatar
        tamerj1
        Community Champion

        HussainWork 
        Two possibilities. Either you have dupicate tags in at lease one of the lookuptables or you have blank tags in at lease one of the tables.