Creating Connection Between 2 Tables Based on Existing Column Condition

[W2SANC]

Hi! 

Newer Power BI user here. I have two taables that I am trying to conncet. One table [Table 1] is a transaction level table, where each sale is a row, and each row has columns (product type, product subtype, total sale amount, city, state, zip, census tract, county, whether that county meets a certain threshold of poverty (poverty status) and comsumer (buyer) demographic infomation (gender,race,whether they live in low-income area)).

In another table [table 2], I have a list of every county in the USA with the columns: state, county, and whether that county meets the poverty status threshold (Y/N binary data). 

In my visuals,  it is showing totals of sales, but I am unable to show a visual of the total of sales in a county that meets the poverty threshold. I need to be able to use this filter on several visuals and can't find a solution online to help me create visuals from [Table 1] and being able to create visuals specific to the ones made in counties that meet the poverty status [Table 2]. I currently have a relationship 1:many in one direction ([Table 2] to [Table 1]). 

Thanks in advance to anyone able to decipher the dax code needed to get Table 1 to be filtered by the poverty status colum in Table 2. 

Any and all help would be appricated! 

[Sahir_Maharaj · Accepted solution]

Hello @W2SANC 

To filter the visual based on the poverty status, you can use a measure along with the RELATED function. The RELATED function allows you to access related columns from a table based on the established relationship:

Total Sales in Poverty Counties = 
CALCULATE(
    SUM('Table 1'[total sale amount]),
    RELATED('Table 2'[poverty status]) = "Y"
)

Do not hesitate to let me know if you might need further assistance.

[W2SANC]

Thanks, @Sahir_Maharaj ! 

Your DAX related function makes sense and seems promising. But, when I go to type in the portion after "RELATED(" there is no drop down like there was for table 1 where I can select the table 2 [poverty status]. If I manually type it, it shows as an error, despite the connection I have between the two. 

This is leading me to think I may be doing something wrong in the relationship. 

Thanks!