Forum Discussion
Use same filters for two different tables
Hello,
I'm stuck in my report and I need your help.
I have two tables with almost the same columns
- A : One with my current data
- B : One is the history of my data except current data
I already did multiples fomulas with measure to compare my two tables and showing the results with cards.
However, I have a problem : when I'm using filters, it only filter in one table and not the other one. Let's say my filter is "Continent", when I filter on "Europe" on table A, I want that my cards take only in account the lines with "Europe" in table A and B. Is it possible to connect two filters with the same name?
I know that I can create an another table with my continents to fix this problem. But I have a lot of filters and I prefer to avoid to create a table for each of my colums.
Thank you very much for your help!
Best regards,
Alex
5 Replies
- amitchandak
Super User
Anonymous , You need to have common dimension table/tables and join them with both
example Continent= distinct(union(distinct(Table1[Continent]),distinct(Table2[Continent])))
Join with both tables and filter using this table
Then you have measure like = sum(Table1[Value]) + Sum(Table2[Value])
do same for date table
https://www.seerinteractive.com/blog/join-many-many-power-bi/
Or append these two tables in power query
Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query Hello amitchandak ,
Thank you for your help. I have a lot of filters (date of creation, date of modification, piece, type, owner, team, etc.). Do I need to create a table for each of them?
Thank you.
Best regards,
Alex
If you wish to have 2 fact tables (current and prior), then the answer is yes. amitchandak's suggestion will require a separate bridging table for each dimension.
The alternative that you may want to consider is to Append the current and prior into the same table after adding a column containing the value "Current" and "Prior" on the respective tables. This would avoid the need for the Bridging tables, but it will requried you to update the DAX measures to something like.
Count of Current = CALCULATE ( COUNTROWS( 'Single Table' ), 'Single Table'[Version] = "Current" )
Count of Prior= CALCULATE ( COUNTROWS( 'Single Table' ), 'Single Table'[Version] = "Prior" )
EA,
I have the same problem.
Please did you find a solution for this!
Regards,
MBA
