Forum Discussion

tecumseh's avatar
tecumseh
Icon for Resolver III rankResolver III
2 years ago
Solved

A Single Value For Column Cannot Be Determined

Hi, Using up-to-date version of PBID. I'm getting an error from LOOKUPVALUE() A Single Value For Column Cannot Be Determined.... I don't understand that. I checked the Table I'm trying to g...
Need Help
  • tecumseh's avatar
    tecumseh
    2 years ago

    Thanks Anonymous ,

    I think, in the end, the issue was that I was trying create a LOOKUPVALUE from one Calc_Table to another within the same lineage. This was causing a Circular error when trying in Step 6 in the diagram below to apply the Recover Customer List from Step 5 to All Customers in Step 2.

    Instead, I duplicated the Calc Table in Step 02 and renamed it so it is no longer on the same lineage tree and I was able to apply the 21 Recovered Customers from NB_05 to the new Summary_For_Recovered Calc Table (Orange in the diagram). So now I have a Table with 133 record flagged as Recovered.

    Thanks,
    -w

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi tecumseh ,
Based on your description, I tested it and the duplicate values in the fact table you mentioned really don't affect the syntactic structure of the LOOKVALUE expression, but I noticed that your third parameter in LOOKUPVALUE uses a column in the table, which could be the reason for the error. To fix this, you can prefix the third argument with SELECTEDVALE to make sure that it is the only value to be judged.
Here is my test data:
Dim

Fact

 

Measure = 
LOOKUPVALUE(
    'FACT'[VALUE],
    DIM[VALUE],
    SELECTEDVALUE('FACT'[VALUE])
)

 

Final output

Reason

 



Best regards

Albert He

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • tecumseh's avatar
    tecumseh
    Icon for Resolver III rankResolver III
    2 years ago

    Thanks Anonymous ,

    I think, in the end, the issue was that I was trying create a LOOKUPVALUE from one Calc_Table to another within the same lineage. This was causing a Circular error when trying in Step 6 in the diagram below to apply the Recover Customer List from Step 5 to All Customers in Step 2.

    Instead, I duplicated the Calc Table in Step 02 and renamed it so it is no longer on the same lineage tree and I was able to apply the 21 Recovered Customers from NB_05 to the new Summary_For_Recovered Calc Table (Orange in the diagram). So now I have a Table with 133 record flagged as Recovered.

    Thanks,
    -w