Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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])
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
@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])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
98 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |