Table visual with data from 2 different data table only showing common data

[KC_MS]

i created a table visual getting data from 2 different data tables but it only shows data which is common in both table. Anybody have some idea why and how to resolve?

table A is a main table with a lots of column and with a unique key column "Case ID".

it's imported from a pbi dataset created by other department which we can use but can't make any change

Case ID	Column 1	Column 2	...
case1	...	...	...
case2	...	...	...
case3	...	...	...
case4	...	...	...

table B is a supplementary info with some info for a few special case, also having the "Case ID"

it's imported from a sharepoint list

Case ID	Column A	Column B
case3	...	...
case4	...	...

the visual is structured and own show records like below, i.e. only showing the comon records

Case ID (from table A)	Column 1	Column 2	... (some other columns from table A)	Column A	Column B
case3	...	...	...	...	...
case4	...	...	...	...	...

but i would like it to be like this, i.e. show all the cases in table A even it doesn't existing in table B

Case ID (from table A)	Column 1	Column 2	... (some other columns from table A)	Column A	Column B
case1	...	...	...	(blank)	(blank)
case2	...	...	...	(blank)	(blank)
case3	...	...	...	...	...
case4	...	...	...	...	...

i rarely use data from others dataset to built anything but i think what i wanted is the "normal" behaviour when i'm having eveything within the my own report. 

so i suspect that the problem is because part of my data are imported from other dataset. But i'm not sure.

anyone has idea? if the problem is really due to part of my data is imported from other's dataset, anyway to resolve?

Thanks!

[Ashish_Mathur]

Hi,

Create a Dim table of Case ID.  Create a relationship (Many to One and Single) from each Fact table to this Dim table.  To your visual, drag, the Case ID from the Dim table.

[DataInsights]

@KC_MS,

One approach is to create a DAX calculated table consisting of all Case ID values in the two tables. Example:

Case ID Master =
DISTINCT (
    UNION ( DISTINCT ( 'Table A'[Case ID] ), DISTINCT ( 'Table B'[Case ID] ) )
)

The table 'Case ID Master' should have a relationship with 'Table A' and 'Table B'. Use 'Case ID Master'[Case ID] in the visual.

[KC_MS]

this seems created another problem...
it cannot be refreshed with an error like this "We cannot refresh this dataset because the dataset contains calculated tables or calculated columns based on data from a Single Sign-on (SSO)-enabled Direct Query data source. Please configure the dataset to use an explicit connection with granular access control to access this data source and then try again."
any other option?

[DataInsights]

@KC_MS,

See the article below for more details.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models#calculated-table... 

Are you able to access the underlying data source of 'Table A'[Case ID]? If so, you could import it into your model and union it with 'Table B'[Case ID], forming a master Case ID table.

[KC_MS]

that works~
do you know is that beacuse my Table A was imported from an separate dataset causing such problem? or sth else?
any idea why such behavior?