Data in common among more than one rank

[nicoams]

Hi guys!

Those ranks are based on the sector and they return the name and performance. I need a table that shows the names that apperas in all of them, but I have no idea how to do that. 

You can download the file here.

Below you can see some images.

Data Table

[HotChilli · Accepted solution]

2 suggestions:

1 - Create a 'super' measure that returns a boolean if the name is in the top 5 of the individual measures e.g.

MTop3M6M12M = [Rank 3M] < 6 && [Rank 6M] < 6  && [Rank 12M] < 6

Now, this doesn't work with your measures (it returns true for all names) because if it's not in the top 5 , the measure returns blank which is evaluated as 0 (I think, can't quite remember, anyway it's evaluated as 'less than 6'), so you would have to add 'test against 0' for each measure (e.g [Rank 3M] <> 0 && .....)

  OR simplify your original measures to remove the 'If' statements - and use visual filtering instead (you can apply filters on visuals using measures in the filter pane). Just add a 'is less than 6' for the correct measure in each visual

That leads me to suggestion 2.

Use visual filtering in the individual table visualisations for each measure. Remove the If statement first in each measure.

Use visual filtering in the 'common names visual' - this one will have 3 measures in it and 3 filter conditions (one for each measure, filtering to be less than 6)

Let me know how you get on

[nicoams]

Hi, @HotChilli 

I made the following adaptation on your first suggestion and it works perfectly. Thank you very much!

MTop3M6M12M = 
var BigTest = [Rank 3M] <> 0 && [Rank 3M]<= 5 && 
            [Rank 6M] <> 0 && [Rank 6M]<= 5  && 
            [Rank 12M] <> 0 && [Rank 12M]<=5
return
IF(BigTest=TRUE();"1";BLANK())

The results:Result: no matchResult: one matchResult: more than one match