topic Extracting information from second table using ID (dataset data model so measures only?) in DAX Commands and Tips

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

Anonymous — Fri, 25 Mar 2022 10:08:53 GMT

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

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

johnt75 — Fri, 25 Mar 2022 10:21:50 GMT

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

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

goncalogeraldes — Fri, 25 Mar 2022 10:32:49 GMT

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

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

Anonymous — Fri, 25 Mar 2022 14:22:07 GMT

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