Merge columns in Paginated report

[rnola16]

Hi,

I have 3 entirely different  queries.

Q1: NAME, ID, SALES_AMT, LOC_CNT

Q2: NAME, ID, PHYS_CNT, REJ_CNT, BILLED_AMT

Q3: NAME, ID, NET_CNT

How can I achieve a table for all values of NAME and ID with results as

NAME, ID, SALES_AMT, PHYS_CNT, NET_CNT.

I'm looking to have a FULL OUTER JOIN between the 3 data sets when I put these values in the Tablix.

Tried using the Lookup function but that only brings the matched records which exist between the data sets. How can I bring in all the values which results from the 3 datasets ?

Results from Q1:

John	X1234	26	72
Steve	Z2134	34	24
Frank	D2145	56	31

Results from Q2:

Grace	D2134	72	86	99
Jill	F1342	31	29	20

Reulsts from Q3:

John	X1234	88
Joe	F1341	90
Liz	Z1423	61
Beth	H7123	72

Desired Output:

NAME	ID	SALES_AMT	PHYS_CNT	NET_CNT
John	X1234	26		88
Steve	Z2134	34
Frank	D2145	56
Grace	D2134		72
Jill	F1342		31
Joe	F1341			90
Liz	Z1423			61
Beth	H7123			72

Thanks.

[v-ssriganesh]

Hi @rnola16,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @rnola16,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[rnola16]

I can do this using UNION by combining all the queries, but there comes a point when I have like 10 different queries ( with different number of col in each) and  merge on field to retrieve other col values in the tablix. Is report Builder equipped to merge columns on a field with muliple datsets ?

Appreciate your response @Bmejia .

[v-ssriganesh · Accepted solution]

Hello @rnola16,
Thank you for reaching out to the Microsoft Fabric Forum Community.

To answer your question the Report Builder doesn’t support merging datasets like a SQL JOIN or Power BI relationships.

You can use the Lookup() function to fetch values from other datasets, but that only works one-to-one (like a left join). If you have 10+ datasets, this means you'll need to:

• Use a master dataset that contains all distinct ID/Name keys (via UNION)
• Use Lookup() for each column you want to bring in from the respective dataset.

Alternatively, if you're able to, building a SQL View or Stored Procedure that handles the full outer join across your datasets will give you the cleanest and most scalable approach.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hi @rnola16,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

[Bmejia]

Can you do something like this

Table =

UNION(

    SELECTCOLUMNS(Q1,

    "Name",Q1[NAME],

    "SALES_AMT",Q1[SALES_AMT],

    "PHYS_CNT","",

    "NET_CNT",""

    ),

    SELECTCOLUMNS(Q2,

    "Name",Q2[Name],

    "SALES_AMT","",

    "PHYS_CNT",Q2[PHYS_CNT],

    "NET_CNT",""

    ),

    SELECTCOLUMNS(Q3,

    "Name",Q3[Name],

    "SALES_AMT","",

    "PHYS_CNT","",

    "NET_CNT",Q3[NET_CNT]

    )

)