Help in Error: a table with multiple values

[HussainWork]

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

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

[tamerj1]

Hi @HussainWork 
In which table are you creating this column?

[HussainWork]

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]

@HussainWork 
I'm sorry. After having a second look I realized the issue. Actually you are retrieving the single value from the dimention table then use that value to retrieve a value from another fact table. But this will return a table not a single value. In this case LOOKUPVALUE is useless as it returns blank which creates other problems in the subsequent LOOKUPVALUES. 
Alternativerly, you can rely on RELATED and RELATEDTABLE. You can utilize CLACULATETABLE to retrieve the table from other fact tables CROSSFILTERing the relationship between the dimention and the other fact table to "Both". In this case you need to provide some sourt of agregation like MAX or MIN.

[tamerj1]

@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.

[amitchandak]

@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]

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).