Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
W2SANC
Helper I
Helper I

Creating Connection Between 2 Tables Based on Existing Column Condition

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! 

 

1 ACCEPTED SOLUTION
Sahir_Maharaj
Super User
Super User

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.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

2 REPLIES 2
Sahir_Maharaj
Super User
Super User

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.


Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution?
➤ Lets connect on LinkedIn: Join my network of 13K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

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! 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.