I have the following tables: Opportunities, Videos, Songs, Requests. Each Opportunity has many Videos and each Video contains a Song. If someone makes a Request, it makes a new row in the Requests table which contains a Request ID, the Opportunity ID, ref values for the Videos, and ref values for the Songs.
I need to pull the Request ID into the corresponding rows of the Songs table so that I know "โHey Judeโ is included in Request 1 ". I tried the following expressions in a virtual column in the Songs table but nothing worked.
SELECT(Requests[Request ID],IN([Song ID], Requests[Songs])
SELECT(Requests[Request ID],IN([Song ID],SPLIT(Requests[Songs],",")
SELECT(Requests[Request ID,CONTAINS([Song ID],Requests[Songs])
Any ideas?
Solved! Go to Solution.
Iโve read both of these many times, this is a little different. Use the following example.
Requests Table
Request ID = 1, Opportunity ID = 123, Song IDโs = 1001,1002, 1003, 1004,1005
Songs Table
Song ID = 1001, Song Title = 'Hey Jude, Request ID = ?
Song ID = 1002, Song Title = โThrillerโ, Request ID = ?
Song ID = 1003, Song Title = 'A Song Steve Wrote (Unreleased), Request ID = ?
The Request IDs are getting generated when users submit new requests. From the Songs table I need to say "for any requests that include my Song in the list of Song IDs, give me the Request ID. I canโt dereference from the songs table because I donโt have the reference Id yet. Instead, from the Requests table Iโm using an action โExecute on action on a set of rowsโ and Iโm able to fire on the correct rows, but Iโm not able to tell the action in the Songs table which Request ID to pull in.
Ah.
FILTER(
"Requests",
IN([_THISROW].[Song ID], [Song IDs])
)
Ha, that worked. Not sure how this is different from first Select expression I mentioned but, as always, thanks Steve!
User | Count |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |