See all rows in tabel visualisation

[Anonymous]

Hi all,

I am trying to combine 2 tables into 1 from 2 different sources.

When I made the relationship and try to use the table visualisation, not all the IDs is being displayed, is there any way I can show all IDs in the table and rows where no data will show the data as blank?

(I simplified it for easier understanding) 

Table 1:   

1

2

3

4

5

3

3

Table 2:

2	$100
3	$85
5	$55
9	$75

The 2 columns that I am trying to form a relationship with is the 2 IDs and I am expecting this output:

1
2	$100
3	$85
3	$85
3	$85
4
5	$55

The current relationship they have now is a many to many relationship for ID. The expected data may not make sense but other columns which exist. I just wanted to simplify it.

Many thanks in advanced 🙂

[Anonymous]

Hi @Greg_Deckler 

I tried doing this and the data still only allows me to select many to many relationship which is odd.

Is there a reason for this?

Thank you for your help

[Greg_Deckler]

@Anonymous - It's really, really hard to say without seeing how everything is configured and laid out. Any screen shots you could post of what you are seeing and how the visuals are configured would be extremely helpful. Can you share the PBIX? Can you share a mock-up PBIX of your data where we can witness what you are seeing? These things are notoriously difficult to troubleshoot. I don't even know what exactly your calculation is.

The screen shot as well as the attached PBIX file (below sig) Page 35, Table (35) and Table (35a) show that it is indeed possible to achieve:

[Anonymous]

Hi @Greg_Deckler 

Let me try to explain it in a different way.

This is what I see on my end. The connection from Datasheet to cable schedule works fine. I am currently trying to show the values of the RunAmp from the loadlist for Tag number (unique values) which matches the Cable Number (has duplicates) from the Load List. The Cable Schedule has items which is not in the Tag number of the loadlist and the loadlist also has items which is not in the Cable Schedule. 

But when I do that, each and every value of the RunAmp is being printed out for every single row.

I am unable to share my pbix as it contains sensitive information.

Thank you very much for your help.

[Greg_Deckler]

@Anonymous - Ah, your bridge table still contains duplicates. This can happen in certain circumstances when you have a text-based key. Did you use DISTINCT? I think it can still happen though depending upon casing or there is some other hokey reason for this. Also, your relationship direction from your bridge table is not correct for the right-hand table, it should be pointing the other direction or Both <>.

[Anonymous]

Hi @Greg_Deckler ,

I used 

Bridge Table =

DISTINCT(

UNION(

SELECTCOLUMNS('Cable Schedule',"ID",[Cable Number]),

SELECTCOLUMNS('loadlist',"ID",[Tag Number])

)

)

I have changed the relationship and I still get the result below being repeated for every single row. The numbers on the right is the RunAmp.

I cant seem to just get the value for that which matches the Cable Name.

[Greg_Deckler]

@Anonymous - Is your ID column text or numeric? Can you provide me a sample of the values there or just examples of what they look like? DAX has a nasty case insensitivity habit.