Table Visual Using Multiple Data Sources

[jsuttmann]

I'm new to Power BI and am trying to build a Table visual using multiple connected data sources. The Matrix visual addresses this, but does not allow the formatting I need, so I'm trying to make this work with the Table visual. Where I need help is returning specific values based on ID, but my only options are First and Last.

HEADER: Data Source 1

HEADER_ID	EVENT
101	A
102	B
103	C

DETAIL: Data Source 2

HEADER_ID (fk)	TYPE_ID	RESULT
101	91	abc
101	92	def
102	91	ghi
102	92	jkl
103	91	mno
103	92	pqr

I'm trying to get the results to show as follows in the Table visual:

EVENT	RESULTS-A (where TYPE_ID=91)	RESULTS-B (where TYPE_ID=92)
A	abc	def
B	ghi	jkl
C	mno	pqr

When I add the two RESULT fields, my only options are "First" and "Last", which can work but makes the assumption that the data is pre-sorted by TYPE_ID, which I can't guarantee. Is there a way to ensure that the two RESULT columns are returning the correct value based on TYPE_ID?

[jsuttmann]

Thank you @Greg_Deckler !  Even though I don't fully understand the syntax, the appears to work exactly as intended, regardless of the database order!  I appreciate your help

[Greg_Deckler · Accepted solution]

@jsuttmann You could do something like this:

RESULTS-A Measure = 
  VAR __HeaderID = MAX('DataSource1'[HEADER_ID])
RETURN
  MAXX(FILTER('DataSource2',[HEADER_ID (fk)] = __HeaderID && [TYPE_ID] = 91),[RESULT])

RESULTS-B Measure = 
  VAR __HeaderID = MAX('DataSource1'[HEADER_ID])
RETURN
  MAXX(FILTER('DataSource2',[HEADER_ID (fk)] = __HeaderID && [TYPE_ID] = 92),[RESULT])