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

[joebc]

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:

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

[v-piga-msft]

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

[joebc]

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]

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