Unable to create report from 2 Transaction tables connecting to a master table

[Roy_tap]

Hi,

I was trying to create a report based on 2 trx tables connecting to one master table using the same Key as shown below:

The sample data is as follows:

MST_FA			TRS_FA				TRS_PO
FAID	FANAME		AMT	FADATE	FAID		FAID	POAMT	POL	POLNAME
F001	F001		100	2020/11/11	F001			100	P001	POL001
F002	F002		200	2020/12/13	F001		F002	300	P002	POL002
F003	F003		300	2020/11/11	F002		F002	400	P003	POL003
			400	2020/12/13	F002

How can I achieve the following report with following fields as follows.

FAID    FADATE          POL      AMT    POAMT

I am getting error once i insert POL into the report.

Thanks.

[v-janeyg-msft]

Hi, @Roy_tap 

It’s my pleasure to answer for you.

According to your description,the data type of 'POL' column is text. When there are two different POLs under one FAID, they can't be aggregated, so an error is reported.You can choose other ways to display.

Like this:

What do you want the POL column to display?

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[Roy_tap]

Hi Janey,

Thanks for your reply. Is this suppose to be how a relationship behaves? I understand that, by assigning one of the column i.e. either FADate set to Earliest OR POL set to First, it will work but somehow one of the value will be aggregated automatically. However, the result i am expecting is to have the records from both tables TRS_FA and TRS_PO to be displaced instead of aggregating one of them. From Database standpoint, it is like a 2 full Join for the 3 tables. 

Thanks

Best Regards,
Roy

[v-janeyg-msft]

Hi, @Roy_tap 

Do you want to show  like this:

Best Regards

Janey Guo

[Roy_tap]

Hi Janey,

I am looking at something like the following:

FAID  FADate           POL      AMT        POAMT
F001  2020/11/11    -           100          -
F001  2020/12/13    -           200          -
F002  2020/11/11    P002    300          300
F002  2020/11/11    P003    300          400

F002  2020/12/13    P002    400          300

F002  2020/12/13    P003    400          400

Thanks.

[v-janeyg-msft · Accepted solution]

Hi, @Roy_tap 

You can merge the two tables in PQ, then display the required fields, and use the new table directly to create visuals in the desktop.

Like this:

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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

[amitchandak]

@Roy_tap , Take sum(TRS_FA[AMT]) and Sum(TRS_PO[POAMT])

take other column as is and try.

You can take ungroupped column either from TRS_PO, TRS_FA.

In this can you have one from TRS_PO - POL