Extracting information from second table using ID (dataset data model so measures only?)

[Anonymous]

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

[johnt75 · Accepted solution]

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

[Anonymous]

Thank you both, let me try those solutions and get back to you

[goncalogeraldes · Accepted solution]

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

[johnt75 · Accepted solution]

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