Not able to join the column from 2 dataset in paginated report using Lookup function

[Manglesh]

I have data coming from 2 dataset(1 Query 2 Store Proc). trying to join the column using Lookup function is not joining.

[Manglesh]

Thankyou my friend for Replying...When i used LookupValue function is throwing me error as " Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case"

If i change that to just Lookup function is not throwing me error but the function is not doing anything is not joining and displaying data as it is like an outer join.

Even i tried using Lookupset but not working. Not sur what ios the workaround here

Thanks

[123abc]

In Power BI, you can use the LookupValue function to join data from two datasets when working with paginated reports. The LookupValue function is used to retrieve a single value from a column in another dataset based on a matching value in a column from the current dataset.

Here are the general steps to use the LookupValue function to join data from two datasets in a paginated report:

1. Create a paginated report in Power BI.

2. Make sure you have two datasets: Dataset 1 (Query) and Dataset 2 (Stored Procedure).

3. Identify the columns in both datasets that you want to use for the join. You should have a common or related column in both datasets to join on.

4. Add a table to your paginated report that will display the data from Dataset 1.

5. Use the LookupValue function in an expression to retrieve data from Dataset 2 based on the matching values in the common column. The syntax of the LookupValue function is as follows:

=LookupValue(lookup_value, lookup_column, result_column, dataset_name)

1. • lookup_value: The value you want to match in the current dataset.
   • lookup_column: The column in the current dataset to match against.
   • result_column: The column in the other dataset (Dataset 2) that you want to retrieve.
   • dataset_name: The name of the other dataset (Dataset 2).

2. Place this expression in the cell of the table in your paginated report where you want the data from Dataset 2 to appear.

3. Preview or generate the paginated report to see the joined data.

Here's an example of how you might use the LookupValue function in your report:

=LookupValue(Fields!CommonColumnInCurrentDataset.Value, Fields!CommonColumnInOtherDataset.Value, Fields!ColumnToRetrieveFromOtherDataset.Value, "Dataset2")

Make sure to replace the placeholders with the actual column and dataset names from your report.

It's essential to ensure that the common column used for the join has the same data type in both datasets. Also, verify that the dataset names are accurate.

If you encounter issues while using the LookupValue function, check your data, column names, and dataset names for accuracy and consistency. Double-check that the common column values exist in both datasets for a successful join.

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