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

[umutkaya79]

Hi,

I have 2 tables : 1)'Exposure - All' and 2)'tbExtractFMTenant'.

1)'Exposure - All'  has columns : 'exposure_analysis_group' and  '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. 

Example: 

'Exposure - All' table :    'exposure_analysis_group'         'tenant_name'

                                                        1                                          x

                                                        2                                          y

                                                        3                                          z

tbExtractFMTenant' table :  trend_query_margin             'tenant_name'

                                                       5.50                                     x

                                                       6.50                                     y

                                                       7.50                                     z

Trying to get the trend_query_margin  based on the 'exposure_analysis_group'  slicer selection.

There are no active relationships between these 2 tables and we don't want active relationship.

Wrote a dax but doesn't give me the trend_query_margin based on the 'exposure_analysis_group' slicer :

Margin =
VAR SelectedGroup = SELECTEDVALUE('Exposure - All'[exposure_analysis_group])
RETURN
CALCULATE(
MAX('tbExtractFMTenant'[trend_query_margin]),
FILTER('tbExtractFMTenant', 'tbExtractFMTenant'[tenant_name] = SelectedGroup)
)

If someone have any advise please. Thank you 

[amitchandak]

@umutkaya79 , Try like

Margin =
VAR SelectedGroup = values('Exposure - All'[exposure_analysis_group])
RETURN
CALCULATE(
MAX('tbExtractFMTenant'[trend_query_margin]),
FILTER('tbExtractFMTenant', 'tbExtractFMTenant'[tenant_name] in  SelectedGroup)
)

of exploring treatas

https://docs.microsoft.com/en-us/dax/treatas-function

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/