Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nicoams
Frequent Visitor

Data in common among more than one rank

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 TableData TableDúvida - Rank 1.pngDúvida - Rank 2.png

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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

View solution in original post

2 REPLIES 2
HotChilli
Super User
Super User

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

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: no matchResult: one matchResult: one matchResult: more than one matchResult: more than one match

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.