Table visual breaks depending on order of chosen columns.

[PhilSmith]

Hey folks

I have three tables:

Items  has  OLDSKU, ITEMID.  ITEMID is unique   Multiple ITEMIDs have same OLDSKU

Licenses has OLDSKU, LICENSE#.  OLDSKU is unique.  Multiple OLDSKUs have same LICENSE#.

Images has ITEMID, IMAGE., multiple ITEMIDs and IMAGEs

There may not be a match in Images for every ITEMID, so I would expect to see a blank for IMAGE, and  I do.

  

If I put OLDSKU then LICENSE# then IMAGE in a table, I get what I expect, including a blank in IMAGE

However,  if I put OLDSKU then IMAGE then LICENSE# in a table, wherever I get a (valid) blank for IMAGE, I get a blank for LICENSE#, even if there is Matching LICENSE#. 

I don't think that is the way this visual is supposed to work.

Any Ideas?

Thanx

Phil

[lbendlin]

Excellent question.  The answer is surprisingly complex.  Depending on your data model (which tables do you consider to be "dimensions", which to be "facts" ?) and the choice of the the connection endpoint the behavior will be vastly different. Think about it as left join vs right join etc. Then add in Power BI's abhorrence of gaps in the dimension key column and the concept of "auto exist"  and you got yourself a nice confusing mess of options.

Get your data model in order, use the dimension columns first, make sure they are not degenerate.

[PhilSmith]

Item is a Fact table, with ITEMID as the Unique Key.  The other two are dimension tables.

There are multiple items with the same OLDSKU.  Licenses is based on OLDSKU,  because one record applies to all items under that OLDSKU.  (Think of it as any T-shirt with a picture of Abby Road on it as OLDSKU, and each size and color of T-shirt  as the individual ITEMID.   Anything under that OLDSKU gets the Beatles as a licensor.)

I think I understand what you are getting at, but the only way I can think of to fix it would be to create another table that expands Licensor by ITEMID and use that.  The data itself is As Is/Where Is.

Does that sound right?

Thanx

Phil

[lbendlin]

Item is a Fact table, with ITEMID as the Unique Key.

Fact tables don't usually have unique keys. That's what dimension tables are for.

[PhilSmith]

Sorry, got that backwards.