Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 | |
Line | Model ID |
Line 1 | A |
Line 1 | A |
Line 2 | B |
Line 2 | B |
Line 2 | B |
Table B | ||
Line | Product ID | Container |
Line 1 | 1 | Box A |
Line 1 | 2 | Box B |
Line 2 | 2 | Box C |
Line 2 | 3 | Box A |
Line 2 | 4 | Box 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!
Why are such simple things in Excel so overly and unceccessarily complicated in Power BI? It's a joke.
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
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.
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."
I've also tried something I found from jeffreyweir's post with a similar result
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |