cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jsuttmann
Helper I
Helper I

Table Visual Using Multiple Data Sources

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
101A
102B
103C

 

DETAIL: Data Source 2

HEADER_ID (fk)  TYPE_ID  RESULT
10191abc
10192def
10291ghi
10292jkl
10391mno
10392pqr

 

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)
Aabcdef
Bghijkl
Cmnopqr

 

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?

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
jsuttmann
Helper I
Helper I

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
Super User
Super User

@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])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.