Forum Discussion

chinma's avatar
chinma
Frequent Visitor
2 years ago
Solved

Create Conditional Column from another RankBased Conditional Column

Hi Team,

I have a table (UniqueVendors) with columns (VendorName,VendorRank). Query for this table: "

UniqueVendors = UNION(DISTINCT('Vendor Vs Inventory'[VMNAME]),DATATABLE("VendorName",STRING,{{"Others"}}))".
 

I am trying to create a conditonal column (TargetVendorName) from another RankBased Column (VendorRank). My condition is :

TargetVendorName = IF(UniqueVendors[VendorRank]<=3, UniqueVendors[VendorName],"Others"). If I press "Enter" button after entering this condition, nothing gets created neither no error displayed.

 Expected result is :

VendorNameVendorRankTargetVendorName
VEND_A1VEND_A
VEND_B2VEND_B
VEND_C3VEND_C
VEND_N4Others
VEND_F4Others
VEND_E4Others
VEND_D4Others
Others4Others
VEND_M4Others
VEND_J4Others
VEND_P4Others

 

Please guide me on this.

Thank you

Need Help
  • parry2k's avatar
    parry2k
    2 years ago

    chinma you can use new RANK function to add the column:

     

    Target Vendor Name = 
VAR __Rank = RANK ( DENSE, ALLSELECTED ( VendorTable ), ORDERBY ( [TotalSales], DESC ), 
MATCHBY ( VendorTable[VendorName] ) )
RETURN
IF ( __Rank <= 3, VendorTable[VendorName], "Others" )

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    chinma you can use new RANK function to add the column:

     

    Target Vendor Name = 
VAR __Rank = RANK ( DENSE, ALLSELECTED ( VendorTable ), ORDERBY ( [TotalSales], DESC ), 
MATCHBY ( VendorTable[VendorName] ) )
RETURN
IF ( __Rank <= 3, VendorTable[VendorName], "Others" )