Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
PhilSmith
Helper I
Helper I

Table visual breaks depending on order of chosen columns.

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.

PhilSmith_0-1679598180342.png  

PhilSmith_1-1679598228156.png

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

PhilSmith_2-1679598278018.png

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#. 

PhilSmith_3-1679598312339.png

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

 

Any Ideas?

 

Thanx

 

Phil

4 REPLIES 4
lbendlin
Super User
Super User

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.

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

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.

Sorry, got that backwards.  

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.