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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
123shorer
Regular Visitor

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

Mi2n
Employee
Employee

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.