Forum Discussion
How to "double" filter?
I am visualizing a table (using force directed visual) that contains three columns: From_City, To_City, Visitor_Count
I want to create a single filter (on the visual) that filters both From_City and To_City in the same time, so that users of the visual don't have to deal with two filters: a From_City filter and a To_City filter.
What I tried but didn't work: Created a lookup table with a single column, City, and then relate City to both From_City and To_City, but PowerBI doesn't allow both relations to be active in the same time, so a City-based filtered cannot filter both columns in the same time.
Does anyone have a solution to this? (any help will be appreciated)
Note: I do understand that function-wise such a "double" filter is not the same as two seperate filters, but it is appropriate in my case.
----------------------------------------------------
Update:
To articulate, I want to use the city filter to select cities so that:
1) connections between selected cities show up in the visual, AND
2) connections that involve non-selected cities don't show up
So far I was able to achieve 1) thanks to ImkeF's help but still can't achieve 2)
15 Replies
- ImkeF
Community Champion
You've described the dilemma well, so we need some creative modelling here:
If we can't consolidate 2 filters to 1 data-table, we're going to connect 1 filter to 2 consolidated (appended) data-tables instead.
let Source = From, #"Replaced Value" = Table.ReplaceValue(Source,"From","To",Replacer.ReplaceText,{"FromTo"}), #"Appended Query" = Table.Combine({#"Replaced Value", From}), #"Added Custom" = Table.AddColumn(#"Appended Query", "City", each if [FromTo]="To" then [To_City] else [From_City]) in #"Added Custom"Check out the file.
You duplicate your fact tables, but one of them having the From-City (From) as the consolidating attribute and the other one the To-City (current query of the code above).
The visual you're using is ignoring duplicates, so the new consolidating column will act as your required filter that shows only the selected cities - and their connections, which is essential here.
ImkeF, Thanks for the prompt response.
I understand your idea (clever!) but don't quite understand how to impliment (I'm new to PowerBI), so bear with me here--
1) What does consolidation attribute do? Where and how do I set consolidation attribute for a table?
2) Does the code create the 2nd table (i.e. the appended table), or do you have to duplicate the first table first and then modify it with the code?
3) I noticed that in your example file the From table and the To table have a many-to-one relation on Visitor_Count. Is it OK when my data has duplicates across To-From pairs (i.e. multiple To-From city pairs have the same number of Visitor_Counts)?
Thanks,
- ImkeF
kyle_I,
You (and your questions) are welcome :-)
1) It's the new column 'City' that acts as you (new consolidated) filter-column
2) I did all this using the UI only, so this is how it should work for you as well
- add a column 'FromTo' to your original table (you don't have to rename this query, ours is called "From" in this example)
- create a new query by referencing your original table
- check column 'FromTo' and replace 'From' by 'To'
- append the first query (From) to this query
- create your new filter column 'City' with the logic in the formula
3) I didn't realize there was a connection between these tables - must have been created automatically in the background. You can delete it.
Can you simply create a custom "From-To" column?
E.g. From-To Column = [Name of your From City Column] & " - " & [Name of your To City Column].
Now, if you had Berlin as "from" city, and Sydney as "to" city, the value in the new column would read "Berlin-Sydney".
It is pretty basic, but doesn't it solve the problem? (as long as you don't care about the direction, because obviously you'll also have "Sydney - Beriln" value for the reverse direction)
Thaks to taraskaduk for chiming in.
Indeed I don't care about direction of connection, but the challenge remains: How to use one filter to filter cities from two columns? the columns are From and To if I cared about the direction, or City_A and City_B if I didn't care about the direction.
If I understand your suggestion correctly-- correct me if I didn't-- You are suggesting creating a From-To column and let users filter based on this column. While this is certainly a solution, practically there will be too many From-To pairs in the filter box. For instance, if the user wants to filter out connections with Berlin, s/he will have to uncheck Berlin-London, Berlin-Seattle, Berlin-Sydney, and so on so forth.
Ideally, the user only needs to uncheck Berlin from the filter box to filter out all connections with Berlin. (and this is where I'm pulling my hairs trying to figure out...)
OK, I guess I didn't quite understand what exactly you were trying to accomplish with this.
If you want the users to see all the connections with Berlin (as opposed to a specific lane of traffic), you may want to create an exact same table as the one you have right now, and then create that lookup table with cities you were talking about. Now, connect both tables to the lookup table, one by the from city, one - by to city.
Now, when you click on, say, Berlin, in your lookup table, you can get both connections from Berlin and connecions to Berlin.
