cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Text slicers to filter unrelated tables?

I have 2 tables that I cannot set up a direct relationship between because it will cause a circular relationship with other intermediate tables.  But both tables have a matching column title and text data within the column.  Is there a way to use a slicer from the matching column on table A for that will also filter the data from table B?

 

For example 

Table A
LineModel ID
Line 1A
Line 1A
Line 2B
Line 2B
Line 2B

 

 

Table B
LineProduct IDContainer
Line 11Box A
Line 12Box B
Line 22Box C
Line 23Box A
Line 24Box A

 

I'd like to be able to filter to Line 1 which shows that Product ID "2" is packaged in Container "Box B", whereas for Line 2, the same Product ID "2" is packaged in Container "Box C."  But at this point I won't be able to relate tables A and B together directly by putting a table with distinct "Line" values because it will create a circular relationship with other tables I have in the model.  I'd be happy to provide more detail if needed!

5 REPLIES 5
Regular Visitor

Why are such simple things in Excel so overly and unceccessarily complicated in Power BI? It's a joke. 

Microsoft
Microsoft

You can create a measure using TREATAS() function, which can pass values in one column into another column with the same set of values.

 

SQLBI has an excellent article on the TREATAS() function in the below link.

 

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

 

Regards,

Mi2n

Anonymous
Not applicable

Mi2n,

Thanks for the response!  I did read up on the Treatas function and it seems like a great tool. 

 

It looks like the Treatas function would work great if I were attempting to perform a calculation on integers or whole numbers.  Where I'm getting stuck is I'm simply trying to apply a text filter across tables that don't have a relationship defined.  No mathematical calculations are required here, I just want to show the correct container (text data in Table B) by filtering to the Line (also text data but from Table A) via a slicer.  I'm just getting started with DAX, so forgive me for perhaps a simple question.

Anonymous
Not applicable

I've tried wrapping the Treatas function in a calculatetable like this so far, but when I go to create the visualization, I cannot insert fields from both the new calculated table and the table A.  It throws an error saying that "Power BI cannot determine the relationship between two or more fields."

 

Line Filter on PFEP = CALCULATETABLE(TableB,
TREATAS(
VALUES(TableB[LINE]),
TableA[LINE]
       )
)
 
Anonymous
Not applicable

I've also tried something I found from jeffreyweir's post with a similar result 

 

2. Line Filter on PFEP = CALCULATETABLE(FILTER(
ALL(TableB),
TableB[LINE] in VALUES(TableA[LINE])
)
)
 
I'm unable to display fields from the new calculated table and the original Table A in the same visualization.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors