topic Creating Connection Between 2 Tables Based on Existing Column Condition in DAX Commands and Tips

Creating Connection Between 2 Tables Based on Existing Column Condition

W2SANC — Fri, 23 Jun 2023 19:30:43 GMT

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!

Re: Creating Connection Between 2 Tables Based on Existing Column Condition

Sahir_Maharaj — Fri, 23 Jun 2023 20:35:37 GMT

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.

Re: Creating Connection Between 2 Tables Based on Existing Column Condition

W2SANC — Mon, 26 Jun 2023 17:31:28 GMT

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!