cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SSWADOOD
Frequent Visitor

USE RANKX AS SLICER ON TABLE VISUAL HAVING COLUMNS & MEASUREMENTS FROM DIFFERENT TABLES

Hello,

i am in need of assistance, i am creating a dasboard which has a table visual which includes columns from different tables and different measurements.

 

i have been able to get my desired output which is to get top 10 clients based on their premium which works fine when my table visual only has columns but as soon as I add a measure it stops working. 

 

i am using the following measures for ranking slicer

GROSSPREMIUMRANKVALUE = //RANKS PREMIUM OF CLIENTS IN THE TABLE VISUAL//
RANKX(CROSSJOIN(ALL(DETAILS[CLIENT_NAME],DETAILS[POLICY_NUM],DETAILS[EFF_DATE],DETAILS[EXPIRY_DATE]),ALL(PARNTER_DETAIL[REGION])),[GROSS_PREMIUM],,0)

 

SelectedTopNValue = //SELECTED RANKS VALUE IN THE TABLE VISUAL//
IF (HASONEVALUE(RANK_SLAB[SLAB]),VALUES(RANK_SLAB[RANK]),MAXX(VALUES(DETAILS[POLICY_NUM]),[GROSS_PREMIUM]))

Where RANK_SLAB is a table for rank values 

SSWADOOD_0-1627717251395.png

GROSSPREMIUMVALUE = //measure added in table visual to display gross premium//
IF([GROSSPREMIUMRANKVALUE]<=[SelectedTopNValue],[GROSS_PREMIUM])

 The result is perfect that shows 20 top gross premium clients

SSWADOOD_1-1627717670624.png

but as soon as i add a measure in visual it displays all the rows 

SSWADOOD_3-1627718059898.png

how can i fix this issue,  even i add measures in the visual it shows only the selected top 20 rows.

 

thanks and regards

ssw

 

 

  

2 ACCEPTED SOLUTIONS
Pascal_KTeam
Resolver I
Resolver I

the measure which returns the rank also needs to be added to the visual filter and then simply filter values <=10

View solution in original post

SSWADOOD
Frequent Visitor

@Pascal_KTeam 

 

any idea how can i do the same thing for percentage column

 

CLAIMRATIORANKVALUE = 
RANKX(ALL(DETAILS[POLICY_NUM]),[CLAIM_RATIO],,0)
SelectedTopNCRValue = 
IF (HASONEVALUE(CLAIMRATIORANK_SLAB[CLAIMRATIOSLAB]),VALUES(CLAIMRATIORANK_SLAB[CLAIMRATIORANK]),MAXX(VALUES(DETAILS[POLICY_NUM]),[CLAIM_RATIO]))
CLAIMRATIOVALUE = 
IF([CLAIMRATIORANKVALUE]<=[SelectedTopNCRValue],[CLAIM_RATIO])

currently its working great when using slicer but when i remove the slicer it does not display all values as the [claim ratiovalue] measure should

SSWADOOD_0-1627897932171.png

when i apply the filter

SSWADOOD_1-1627898025239.png

 

 

 

 

View solution in original post

3 REPLIES 3
SSWADOOD
Frequent Visitor

@Pascal_KTeam 

 

any idea how can i do the same thing for percentage column

 

CLAIMRATIORANKVALUE = 
RANKX(ALL(DETAILS[POLICY_NUM]),[CLAIM_RATIO],,0)
SelectedTopNCRValue = 
IF (HASONEVALUE(CLAIMRATIORANK_SLAB[CLAIMRATIOSLAB]),VALUES(CLAIMRATIORANK_SLAB[CLAIMRATIORANK]),MAXX(VALUES(DETAILS[POLICY_NUM]),[CLAIM_RATIO]))
CLAIMRATIOVALUE = 
IF([CLAIMRATIORANKVALUE]<=[SelectedTopNCRValue],[CLAIM_RATIO])

currently its working great when using slicer but when i remove the slicer it does not display all values as the [claim ratiovalue] measure should

SSWADOOD_0-1627897932171.png

when i apply the filter

SSWADOOD_1-1627898025239.png

 

 

 

 

View solution in original post

Pascal_KTeam
Resolver I
Resolver I

the measure which returns the rank also needs to be added to the visual filter and then simply filter values <=10

View solution in original post

Thanks a lot @Pascal_KTeam 😃

 

 in the visual filter changed the measured column to "is not Blank" and it works perfectly. 

 

SSWADOOD_0-1627738565674.png

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.