cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
joebc Frequent Visitor
Frequent Visitor

how to... lookupvalue? Related? related table?

I want to return a value based on a measure from a related table... Very new still to Dax.

My data has a column 'VACANT' with boolean True or False Values. I have a measure ('TF') that calculates to True or False depending on whether the filtered Pivot Table results contain only 'TRUE' values in the 'VACANT' column or not for an ID.

 

I want to be able to use this measure to lookup and return values from another table based on the ID column and the Measure result. So if the pivot table is filtered to ID '123', which could return multiple records and the Measure (TF) is returning 'FALSE', I want the new measure or calulated column to look up the ID '123' and 'FALSE' combination in the related table (RC CI Intervals) and return the result from an adjacent column... This lookup table will have 4 columns and I would want to replicate this measure for each...

 

There is a relationship, but it's a table removed so not sure if that will matter?

 

Example:

2019-08-22_11-07-55.png

 

 

 

 

 

 

 

I've tried modifying various examples I've come across using VALUELOOKUP, RELATED, and RELATEDTABLE, but so far no luck and I'm not really sure which if any of these are even the right track to be on in the first place.

 

Any advice would be greatly appreciated!

 

Thanks

 

3 REPLIES 3
Community Support Team
Community Support Team

Re: how to... lookupvalue? Related? related table?

Hi @joebc ,

If it is convenient, could you share the data sample which could reproduce the scenario and your desired output so that we could help further on it?

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
joebc Frequent Visitor
Frequent Visitor

Re: how to... lookupvalue? Related? related table?

Thanks for the Reply Cherry. I have created a small sample of the data and desired results here: sample data

 

In doing so, i realized my measure isn't quite working as expected so i'll work on trying to figure that out, but even still, i do not understand how to use it in a relationship to return the desired results.

 

In a nutshell, I would like to match up the 'Rate_Code_Merge'[RATE_CODE_ID] and TF measure (the subtotaled result for each ID) values with the 'RC_CI_Interval'[RATE_CODE_ID] and 'RC_CI_Interval'[VACANT] values to return the appropriate matching values from the 4 other columns in the 'RC_CI_Interval' table. After reading some more yesterday, i'm wondering if this a CROSSFILTER problem? or maybe USERELATIONSHIP?

 

Anyways, thanks again for getting back to me and taking time to look at my problem. Please let me know if the link doesn't work or if there is any additional info i can provide to clarify anything.

 

Cheers!

joebc Frequent Visitor
Frequent Visitor

Re: how to... lookupvalue? Related? related table?

After working on this some more, i've found this post https://community.powerbi.com/t5/Desktop/Userelationship-and-Measures/td-p/282305

that i think is similar to the problem i am trying to work on.

 

It seems like USERELATIONSHIP is what i need to figure out in order to create a relationship between the measure [TF] and the 'RC_Intervals'[VACANT] field... i think.

 

It would be much easier and more straight forward if i could just calculate these confidence intervals directly as measures, but for some reason, my version of Excel (2016) does not have the CONFIDENCE function available, which is why i'm trying to create this relationship in order to populate the values from another table where they've been calculated.

 

In the aforementioned post, the poster utilized USERELATIONHIP in a measure in conjunction with CALCULATE and COUNTA like this... 

Enquiry Date = CALCULATE(COUNTA(Leads[EnquiryDate]),USERELATIONSHIP(Leads[EnquiryDate],'Work Calendar'[Date]))

 

But I'm not sure how to modify this measure to suite my needs by creating the realtionship between the [TF] measure and the 'RC_Intervals'[VACANT] column... any advice would be very much appreciated!

 

Thanks

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 379 members 3,632 guests
Please welcome our newest community members: