Help with Relationships

[bhmiller89]

I have two tables/queries loaded.  A relationship was autodected between the "Job Number" fields present in both tables.  When I make a Matrix using data from one table, but then try to add a field from the second table, the data doesn't populate.

I can't figure out why it's not working if there is a successfully created relationship.

[npatten]

Can  you post some pictures, or a sample of the data you're working with? What's the cardinality of the relationship (eg 1-1, 1-many)?

[bhmiller89]

Picuture on left of the relationship that was created between "job number." I've tries both single and "both" directionalities. Picture on right is the Table I'm trying to build and when I check the highlightes portion the data "breaks" and nothing is populated.

[npatten]

I'm not proficient enough (yet!) with DAX and data modeling to fully answer your quetion, but I've run into similar issues. It's possible that the *-1 relationship is causing you some trouble, and that the filtering options offered by the visualizations are not robust enough to handle the complexities of the relationship. It's likely you need to write a DAX formula to explictly make the required filtering.

I have a data model with a number of tables connected via 1-1 relationships, and one table with a 1-*. To get the rows and measures to play nicely I have to be careful about when I use autosuming to get the results to show up correctly. Sometimes tables work more predictably than matrices, too, when crosstabbing. That may be worth a try.

[bhmiller89]

Any suggestions on how to write the DAX formula for filtering?

[bhmiller89 · Accepted solution]

Feel dumb- I had an extra blank space in one of the columns so BI was catching the relationship but it wasn't working.

[Seth_C_Bauer]

@bhmiller89 Don't. It's just a matter of experiencing some of these little gotcha's to remember to check them as you build your model out. Eventually you'll have a long mental list of checks based on experiences like this to figure it out quickly next time. Extra spaces, data types, blanks - all of these at one time or another have cause me headaches.