topic Using LOOKUPVALUE functions gives only few records in CALCULATED COLUMN in DAX Commands and Tips

Using LOOKUPVALUE functions gives only few records in CALCULATED COLUMN

InsHunter — Wed, 19 Jul 2023 13:09:10 GMT

Hi,
Requesting help from experts on a typical problem being faced in using LOOKUPVALUE.
TABLE A -Is a master file with all time stamps in seconds (from a specific start date and time to a specific end date and time) ,for a MeterID,SubscriberID,and need status flag from TABLE B from respective time stamp for respective MeterID,SubcriberID.
TABLE B has a status on a time stamp only if status is logged in that time stamp,else there will not be time stamp record.There were no duplicate records in both TABLE A and TABLE B when checked for all the three columns combined (Date_Time,MeterID,SubscriberID) ,but status column has many repetitions as its a categorical type field.
TABLE A (Change_Log_Master)

TABLE-B (tmMeterReadings_StatusChangeLog)

The DAX iam using for "Status" CALCULATED COLUMN Is

Status =

LOOKUPVALUE(tmMeterReadings_StatusChangeLog[Status],

tmMeterReadings_StatusChangeLog[Date_Time],Change_Log_Master[Date_Time],

tmMeterReadings_StatusChangeLog[MeterID],Change_Log_Master[MeterID],

tmMeterReadings_StatusChangeLog[SubscriberID],Change_Log_Master[SubscriberID]

)

But the result is yeilding only a few records in TABLE A , as below in the CALCULATED COLUMN and all other records are blanks.

Being a newbie ,your support on this earnestly appreciated.
I tried to concatenate three columns to create an unique ID in both tables as a workaround and use LOOKUPVALUE but no improvement.

Re: Using LOOKUPVALUE functions gives only few records in CALCULATED COLUMN

Anonymous — Fri, 21 Jul 2023 02:02:53 GMT

Hi InsHunter ,

According to your descriptions, your dax formula is correct, for Lookupvalue function, If there is no match that satisfies all the search values, a blank is returned. In other words, the function will not return a lookup value if only some of the criteria match. This also happens when the expected result is a Boolean data type.

For more details, you can read related document: LOOKUPVALUE function (DAX) - DAX | Microsoft Learn , LOOKUPVALUE – DAX Guide

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Using LOOKUPVALUE functions gives only few records in CALCULATED COLUMN

InsHunter — Fri, 21 Jul 2023 10:22:51 GMT

Thanks for your response.I even checked the data type in both tables and they are same. As per the data there should be match as the TABLE A has all time stamps with seconds as unit and the other TABLE B from which matching records are fetched has some of the time stamps as per real life occurence. Looks strange. Is there any other probable reasons for such anomally.

Re: Using LOOKUPVALUE functions gives only few records in CALCULATED COLUMN

InsHunter — Fri, 21 Jul 2023 15:54:18 GMT

My Apologies. There was an issue with the Power query which generates TABLE A and its fixed now. Now Iam getting all the matching records of TABLE B. Regret for the inconvenience and thanks for the assertion on the LOOKUPVALUE DAX formula.