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

Top Solution Authors