cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SSWADOOD
Helper I
Helper I

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
Helper I
Helper I

@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
Helper I
Helper I

@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

 

 

 

 

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

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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors