kyle_l Tracker

Re: How to "double" filter?

kyle_l — Thu, 31 Mar 2016 23:01:04 GMT

Question on MAX:

Having played with the formula, I found that I can achieve the same effect by putting MAX on both side or neither side of the euqation, like:

Measure = CALCULATE(COUNT('To'[Index]), FILTER('To',MAX('To'[Index])= MAX('To'[Index])))

Measure = CALCULATE(COUNT('To'[Index]), FILTER('To','To'[Index]='To'[Index]))

Whereas ImkeF puts MAX only on the right hand side:

Measure = CALCULATE(COUNT('To'[Index]), FILTER('To','To'[Index]= MAX('To'[Index])))

Can anyone enlighten me why putting MAX only on one side?

I'm new to DAX coming from the SQL world, the way I picture how this formula works is like this:

SELECT COUNT() AS kount
FROM
	(SELECT From_City, To_City
	FROM table 'To'
	WHERE MAX(Index) == MAX(Index)
	GROUP BY From_City, To_City
	)
HAVING kount == 2

The smart thing about ImkeF's DAX formula is that the GROUP BY columns (From_City and To_City) are dynamically determined by the visual so you don't have to hardcode them like in the SQL above.

To articulate my question-- IF this is how the DAX formula works, wouldn't it make sense to put MAX on both sides because there's a (implicit) GROUP BY?

Re: How to "double" filter?

kyle_l — Thu, 31 Mar 2016 18:13:34 GMT

Thanks ImkeF.

I'd like to report back that your solution works perfectly!

Didn't respond back sooner because I tried to understand how your DAX formula works, and being a DAX newbie it took me a few days.

Re: How to "double" filter?

kyle_l — Fri, 25 Mar 2016 23:27:22 GMT

taraskaduk-

Sorry for the confusion. Let me try again--

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

With ImkeF's help I was able to achieve 1) but not 2).

I had actually tried the same lookup table method as your suggested above, but it worked funky. Connections would show up or not show up depending on if the selected city is connected to the City_Lookup through the From column or the To column.

Here's a small PowerBI example that illustrates the situation (in the "taraskaduk solution" tab).

Re: How to "double" filter?

kyle_l — Fri, 25 Mar 2016 20:47:14 GMT

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...)

Re: How to "double" filter?

kyle_l — Fri, 25 Mar 2016 23:48:05 GMT

ImkeF- You did understand my request perfectly, which is to filter out cities that are not selected.

I initially thought that your solution will do just that, but later realized that it doesn't work that way. For instance, when only one city, say Berlin, is checked in the filter box, two connections (i.e. two rows from the data) will show:

1) From=Seattle, To= Berlin, FromTo = To, City= Berlin

2) From= London, To= Berlin, FromTo= To, City= Berlin

...because City= Berlin, which is what the filter specifies.

The number of connections that the filter allows to show depends on how many rows where City is in [list of selected cities].

In other words, it shows every city that are connected to the selected city(ies) instead of filtering out cities that are not selected.

Alas, good thing it's a slow Friday and I'll have time to find ways to crack it ;)

Re: How to "double" filter?

kyle_l — Fri, 25 Mar 2016 19:06:09 GMT

Thank you ImkeF. Your instructions were easy to follow.

There's one little thing though-- this filter hack works great when a user wants to show every city that are connected with selected cities, but it doesn't let the user to filter out cities that are not selected.

For instance, if we checked Berlin and London in the filter box, the visual still shows the two cities' connections with Sydney and Seattle. This is OK when the number of cities is small like in this example, but when the data contains many cities-- if I select 5 cities from the filter (wanting to see where the strong connections are between these 5 cities) the visual will show dozens other cities that are connected to the 5 selected cities, making it hard to see connections between the 5 cities of interest.

This is not a fault of your solution but what people normally want a filter to do-- to filter down the number objects in a visual so they can see better.

I guess one (partial) solution is to add another filter on Visitor_Count, but then, connections between the 5 cities are not necessarily the stronger connections that will still show up after this second filter is applied.

Re: How to "double" filter?

kyle_l — Thu, 24 Mar 2016 01:05:28 GMT

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,

How to "double" filter?

kyle_l — Fri, 25 Mar 2016 23:35:47 GMT

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)

Re: Help needed: Sankey diagram

kyle_l — Thu, 31 Dec 2015 17:18:35 GMT

Thanks, jep!

I upgraded to the latest release of PowerBI desktop and it worked.

Re: Help needed: Sankey diagram

kyle_l — Tue, 29 Dec 2015 16:34:08 GMT

Thanks for pointing me to the example data, BUT it still wouldn't plot (showing only chart title).

Makes me wonder- Do I have download some javascript or d3 component for the visual to show?

FYI, I am using PowerBI desktop. Saved the example data to my local machine and then added the downloaded data to the PowerBI project. Then I try to use Sankey visual with no avail.

Help needed: Sankey diagram

kyle_l — Mon, 21 Dec 2015 17:43:34 GMT

Hi-

Have anyone had success using the Sankey diagram viz that was published recently (mid-December '15) to Visuals Gallery?

I downloaded and imported the visual to PowerBI Desktop but couldn't get it to plot. I tried three different data sets-- from real data sets to a tiny toy data set-- but it simply wouldn't plot. It would show a blank box showing nothing but chart title.

Did I miss a step? Does Sankey viz require a specific data format for it to plot?

Thanks in advance for your help!

-Kyle