Forum Discussion
Multiple filter on separate tables
Hi, I am working in a display of a S curve.
I have 3 tables, named "Baseline", "Actual" and "Forecast". Each table has 3 columns, "Item ID", "Date" and "Type". I coordinate the 3 tables with a Calendar Table, which has a Date relationship with each "Date" column in each table.
Item ID is an unique identifier for each item, which is present in each of the 3 tables where it has the respective Baseline, Actual and Forecast dates.
The display works OK and shows the curve as expected. The problem is that i want to filter the curve by "Type" column. As i established a relationship with the column "Date" with the calendar table, i cannot link the columns "Type" between the 3 tables. So if i select the column "Type" to make a filter from one table, it just filter the data associated with that table and not all of 3 tables. I dont want to put 3 filters as they will show exact same categories and it will be very annoying to select the value 3 times in order to get the data filtered correctly.
It is possible to link the filters for "Type" for the 3 tables? I don't have a preference on how to implement the solution, i just want to have only one filter list in the report to filter the graph.
thanks
2 Replies
raberrio , create a combined table for type and if one table is missing then add static value as type
Type= distinct(union(distinct(Table1[Type]),distinct(Table2[Type]),distinct(Table3[Type])))
Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solution-8e3eccb41bdaPower BI- Power Query: When I asked you to create common tables: https://youtu.be/PqfGW6pl1Sw
Thanks. I did it but it does not work as a filter for the graph, for none of the 3 tables. As example, the types and data for each table is the same, lets see the following example:
Baseline:ItemID Date (dd-mm-yy) Type A1 01-01-23 A A2 05-01-23 A B1 01-02-23 B B2 05-02-23 B C1 01-03-23 C C2 05-03-23 C Forecast:
ItemID Date (dd-mm-yy) Type A1 A A2 A B1 B B2 10-02-23 B C1 03-03-23 C C2 08-03-23 C Actual:
ItemID Date (dd-mm-yy) Type A1 03-01-23 A A2 10-01-23 A B1 05-02-23 B B2 B C1 C C2 C Date table is a calendar table and has a relationship with each table "Date" column. When i put that 3 tables i can generate the S curve graph, but i want to filter by "Type" just for see, for example, Type "A" curve. When i put a filter using "type" from one of that 3 tables, it just filter the data from the corresponding table and not the other 2, giving me an inconsistent graph. So i want to filter the data from the 3 tables at the same time, but i dont want to have 3 filter objets just for filter 1 graph.
