I have a simplified scenario where there are 2 datasets in Power BI report builder. Dataset A is relatively simple which is a SQL query and the result of which gives Store_ID and Store_Name. Dataset B is a stored procedure (in reality it’s very complicated and hard to convert to a SQL query) which takes two parameters (Start_Date and End_Date) and it returns Store_ID, Sales and etc.
My objective is bringing the Store_Name from dataset A to dataset B and I really need to do that for further calculation steps.
What I have tried so far
-
Use
lookupexpression in Add calculated field option. However when I try to run the report, immediately there is an error message:
-
I then tried use
lookupin the inserted table. This worked, however I do need this field in the dataset rather than a table visualization. -
I also tried according to this link (this is using Add Query Field other than Add Calculated Field): https://slavasql.blogspot.com/2020/07/ssrs-use-of-lookup-type-functions.html, however I followed the steps no matter what expression I put, there is always blank result.
Please help how to bring a column from dataset A to dataset B.