Getting a value from a second table by filtering from the first table

Question

Hi,&nbsp;I have 2 tables : 1)'Exposure - All' and 2)'tbExtractFMTenant'. 1)'Exposure - All'&nbsp; has columns : 'exposure_analysis_group' and&nbsp; 'tenant_name'. Every 'exposure_analysis_group' has a specific tenant_name. 2)'tbExtractFMTenant' columns : tenant_name and 'trend_query_margin'. every ''tenant_name'' has their specific 'trend_query_margin'. I have a slicer from the 'exposure_analysis_group' column. I want to get the 'trend_query_margin' based on the 'exposure_analysis_group' selection.&nbsp;&nbsp;Example:&nbsp;'Exposure - All' table :&nbsp; &nbsp;&nbsp;'exposure_analysis_group'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'tenant_name'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; x&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; z&nbsp;tbExtractFMTenant' table :&nbsp;&nbsp;trend_query_margin&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'tenant_name'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5.50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;x&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6.50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;y&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7.50&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;z&nbsp;Trying to get the&nbsp;trend_query_margin&nbsp; based on the&nbsp;'exposure_analysis_group'&nbsp; slicer selection.There are no active relationships between these 2 tables and we don't want active&nbsp;relationship.&nbsp;Wrote a dax but doesn't give me the trend_query_margin based on the&nbsp;'exposure_analysis_group' slicer :&nbsp;Margin =VAR SelectedGroup = SELECTEDVALUE('Exposure - All'[exposure_analysis_group])RETURNCALCULATE(MAX('tbExtractFMTenant'[trend_query_margin]),FILTER('tbExtractFMTenant', 'tbExtractFMTenant'[tenant_name] = SelectedGroup))&nbsp;If someone have any advise please. Thank you&nbsp;&nbsp;

amitchandak · Answer

umutkaya79 , Try like 
&nbsp;
Margin =VAR SelectedGroup = values('Exposure - All'[exposure_analysis_group])RETURNCALCULATE(MAX('tbExtractFMTenant'[trend_query_margin]),FILTER('tbExtractFMTenant', 'tbExtractFMTenant'[tenant_name] in&nbsp; SelectedGroup))
&nbsp;
of exploring treatas
https://docs.microsoft.com/en-us/dax/treatas-function
https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

Forum Discussion

Getting a value from a second table by filtering from the first table

1 Reply

Related content

User Group Leader Get Started Guide

Filter

Get data from Web

Get DP-700 for FREE — Limited Offer

Get data - Json.document