Getting an issue with a Table Visual, blank rows

[SPa]

Hi all, 

I'm simplifying the issue in the below example

I have a fact table and two dimension tables.

The tables are arranged as following:

Table	Column
DIM Consignee	Name
DIM Consignee	Stake ID
DIM Consignor	Name
DIM Consignor	Stake ID
FACT Sub	Case Nr
FACT Sub	Stake ID

DIM tables are connected to the Fact table by 'Stake ID'. (one to many)

I will provide some example data:

[FACT Sub]
Case Nr	Stake ID
Case1	Stake1
Case1	Stake2
[DIM Table 1]
Name	Stake ID
David	Stake1
[DIM Table 2]
Name	Stake ID
Peter	Stake2

What I want is to create a table visual using one column from each table:

[FACT Sub]	[DIM Table 1]	[DIM Table 2]
Case Nr	Name	Name
Case1	David	Peter

However when I do this I get the following:

[FACT Sub]	[DIM Table 1]	[DIM Table 2]
Case Nr	Name	Name
Case1	(Blank)	Peter
Case1	David	(Blank)

Is there a way to group the entries by the common 'Case Nr' in the visual via better data modelling?

[Anonymous]

Hi,@SPa .I am glad to help you.
If you do need to show a similar effect in the table as in the matrix, I recommend that you process the original data, use a merge query, and populate the data up/down to achieve this effect (if there are multiple Case Nr's involved you'll need to populate them in groups). Eventually remove duplicate rows within the same group
Like this:

Because table visual can only present data in one dimension, you need to process the data using Power Query
this is my M code:

let
    Source = Table.NestedJoin(#"FACT Sub", {"Stake ID"}, #"DIM Table 1", {"Stake ID"}, "DIM Table 1", JoinKind.LeftOuter),
    #"Expanded DIM Table 1" = Table.ExpandTableColumn(Source, "DIM Table 1", {"Name"}, {"DIM Table 1.Name"}),
    #"Merged Queries" = Table.NestedJoin(#"Expanded DIM Table 1", {"Stake ID"}, #"DIM Table 2", {"Stake ID"}, "DIM Table 2", JoinKind.LeftOuter),
    #"Expanded DIM Table 2" = Table.ExpandTableColumn(#"Merged Queries", "DIM Table 2", {"Name"}, {"DIM Table 2.Name"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded DIM Table 2",{"Stake ID"}),
    #"Filled Down" = Table.FillDown(#"Removed Columns",{"DIM Table 1.Name"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"DIM Table 2.Name"}),
    #"Removed Duplicates" = Table.Distinct(#"Filled Up", {"Case Nr"})
in
    #"Removed Duplicates"

this is my test data:
[FACT Sub] [DIM Table1] [DIM Table2]
 

Case Nr	Stake ID
Case1	Stake1
Case1	Stake2

Name	Stake ID
David	Stake1

Name	Stake ID
Peter	Stake2

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[Anonymous]

Hi,MFelix ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello,@SPa .I am glad to help you.
I'll try to reproduce what you need.
This is my test data.

The relationship:

However, since there is no relationship between the two Dim tables for filtering, they must create new fields (rows) if they place the same field into the Matrix/Table, there is a many-to-one relationship between them only with the ‘FACT Sub’ table, and there must be aggregate columns in the table visual or Power BI can't both be presented in a visual at the same time.
Power BI filters based on the relationship in the model, where the system recognises the [Name] column in both DIM tables as two fields that are not related at all (even though the only actual difference between them is that the table names don't match)

So I think Mfelix made a very good suggestion.
If your DIM Table 1 and DIM Table 2 have exactly the same fields between them, with only differences before the data, you can merge them into one table

create relationship

Display the filtered data through the created relationships.

Here is the result.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,

[SPa]

Hi and huge thanks for taking the time to reply. 

The Dim tables represent different types of individual. Lets say for example Sales and Engineers. 

Therefore I require a table visual to be used that would display:

Table Columns:

Case Nr

Sales Name

Engineer Name

In example Data, lets say Peter and David relate to the same Case Nr. They would then be in the same row. I cannot use a matrix visual to achieve this as displayed in your examples, i need to use a table visual. 

Once again, I would like to thank you for taking the time to respond to this query! 

(wishing you all a great end to the year!)

[MFelix]

Hi @SPa ,

You need to have a unique Dimension table or merge this data into a single table, but be aware that you need to have a single stake ID for each of the values in the dimension table your data is very small so this works correctly but you need to see if your data will work in the future states: