Forum Discussion
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:
- 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).
- 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
| Tags | Performance Low Limit | Acknowledgement Tags | Acknowlegement Type | Acknowlegement Limit |
| T1 | 230 | A1 | Value | 30 |
| T2 | 260 | A1 | Value | 30 |
| T3 | 370 | A1 | Value | 30 |
| T4 | 510 | A2 | Status | Not Running |
| T5 | 120 | A2 | Status | Not Running |
| T6 | 1500 | A2 | Status | Not Running |
tags_Readings Table
| Tags | Date & Time (Hourly) | Reading |
| T1 | 1/1/2022 00:00 | 50 |
| T2 | 1/1/2022 00:00 | 40 |
| T3 | 1/1/2022 00:00 | 20 |
| T4 | 1/1/2022 00:00 | 100 |
| T1 | 1/1/2022 01:00 | 10 |
| T2 | 1/1/2022 01:00 | 30 |
Acknowledgement_Reading Table
| Acknowledgement Tags | Date & Time (Hourly) | Achknowledgement Value |
| A1 | 1/1/2022 00:00 | 160 |
| A3 | 1/1/2022 00:00 | 4 |
| A4 | 1/1/2022 00:00 | 60 |
| A1 | 1/1/2022 01:00 | 100 |
Acknowledgement_status table
| Acknowledgement Tags | Date & Time (Hourly) | Acknowledgement Status |
| A2 | 1/1/2022 00:00 | Not Running |
| A5 | 1/1/2022 00:00 | Running |
| A2 | 1/1/2022 01:00 | Not Running |
| A5 | 1/1/2022 01:00 | Not Running |
6 Replies
- amitchandakSuper User
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])
- HussainWorkNew 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).
- tamerj1Community Champion
Hi HussainWork
In which table are you creating this column?- HussainWorkNew 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].
- tamerj1Community 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.