Forum Discussion
Extracting information from second table using ID (dataset data model so measures only?)
Hi there,
Bit of a complicated one and as I'm not too experienced with working with PowerBI datasets, I am a little stumped on how to proceed due to the limitations.
What I have currently are two tables. Issue and Issue Attributes. I am trying to extract the data from the Issue Attributes table into the dashboard. Both tables have an Issue_ID number but only Issue has the "Created Date" field which I would like to use as a filter.
Normally I would just create a relationship between the two tables to allow me to reference the Issue table via the Issue ID to get the "Created Date" values but this is a company dataset I am linking to and the data model does not seem to have a relationship modelled between the two. I am also limited to creating measures only and no calculated columns due to the dataset.
I have tried using vlookupvalue and it will return the result I'm looking for but only one instance of it (I cannot seem to pass the list of issue_id as a parameter for searching).
Any ideas? Some sample data below:
Issue
| Issue_ID | Created Date | Created By |
| 1165 | 25/03/2022 | Jack Montogomery |
| 2149 | 13/04/2022 | Mark Mitchell |
| 1032 | 16/06/2022 | Mark Mitchell |
Issue Attributes
| Issue_ID | Issue Type | Issue Details |
| 1032 | Safety | PPE |
| 1165 | Safety | Trip Hazard |
| 2149 | Environmental | Dirty Water |
If the entries in the Issues table are unique, i.e. it would have been a one-to-many relationship, then you can create a measure like
Created date measure = CALCULATE( SELECTEDVALUE(Issue[Created Date]), TREATAS( { SELECTEDVALUE('Issue Attributes'[Issue_ID]) },Issue[Issue_ID] ) )and put that into a visualisation with details from the Issue Attributes table
Hello there Anonymous ! Try using the TREATAS() function to apply filtering contextbetween two unrelated tables. Dont forget to use the VALUES() function the "many" side of the relationship. Something like:
Measure = CALCULATE( SELECTEDVALUE(Issue[Created Date]), TREATAS( VALUES(Issue[Issue_ID]) , 'Issue Attributes'[Issue_ID]) )Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!
You can also check out my LinkedIn!
Best regards,
Gonçalo Geraldes
3 Replies
If the entries in the Issues table are unique, i.e. it would have been a one-to-many relationship, then you can create a measure like
Created date measure = CALCULATE( SELECTEDVALUE(Issue[Created Date]), TREATAS( { SELECTEDVALUE('Issue Attributes'[Issue_ID]) },Issue[Issue_ID] ) )and put that into a visualisation with details from the Issue Attributes table
Hello there Anonymous ! Try using the TREATAS() function to apply filtering contextbetween two unrelated tables. Dont forget to use the VALUES() function the "many" side of the relationship. Something like:
Measure = CALCULATE( SELECTEDVALUE(Issue[Created Date]), TREATAS( VALUES(Issue[Issue_ID]) , 'Issue Attributes'[Issue_ID]) )Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!
You can also check out my LinkedIn!
Best regards,
Gonçalo Geraldes