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

DAX RANKX Function filter

 

Hello Everyone,

I'm new to power BI, I have calculated result to get security name rank-wise, I have use RANKX Function ,code is like this

 

CALCULATE(
    MAX( Sect[Security Name]),
    FILTER(
        VALUES ( ContributionCategory ),
        ContributionCategory[SECTOR] = fourthPortfolio
    ),
    FILTER(Sect,
        Sect[Security Name] <> BLANK()
    ),
    FILTER (
        VALUES(  Sect[Security Name] ),
        RANKX ( ALLNOBLANKROW ( Sect[Security Name] ), AllMeasure[Total Return Contribution],, DESC ) = 1
    )
)

 

here ,I want to get result which shows on first rank, but here I'm getting blank or nonText(empty) value on rank 1 and also in 2,3,4,5 rank.
and I have tried like this on 6 rank
        RANKX ( ALLNOBLANKROW ( Sect[Security Name] ), AllMeasure[Total Return Contribution],, DESC ) = 6

then I'm getting proper result, but I want to ignore all the blank to nontext value in rank, and want six rank value on first rank ,
so how can i Ignore or filter all the blank or nontext value...
can anyone help me ?

 

 

6 REPLIES 6
Community Support Team
Community Support Team

Re: DAX RANKX Function filter

Hi @kjshah5030,

 

Could you please share you sample data or pbix to me so that I can help with that.

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kjshah5030 Frequent Visitor
Frequent Visitor

Re: DAX RANKX Function filter

Hi @v-frfei-msft

Sure ,
Please find pbix file I have put in dropbox
https://www.dropbox.com/s/off4875xjsl7bez/Cyborg%20Equity%2018Q1%20_changeWithShum.pbix?dl=0


Here is the pbix file,In pbix file there is one matrix table,
Let me explain what I'm trying to do , I'm trying to get Top 1 sector Name Based on Highest End Weight(Benchmark) , means you can see in ex , Highest Weight is 23.17 which sector Name is Financials ,and I have got that Name using below Example


var sectorName = CALCULATE(
    MAX(ContributionCategory[SECTOR]),
    FILTER (
        VALUES ( ContributionCategory[SECTOR] ),
        RANKX ( ALL ( ContributionCategory[SECTOR] ), AllMeasure[Total Return Contribution],, DESC ) = 1
    )
)



Now ,I want to get Top 1 SecurityName Based on Highest Total Return Contribution by filtering This sector ('Financial'), means I want 'Financial' sector's Top 1 Security Name Using Highest Total Return Contribution with Ignoring all the Blank and NonText ...

Let me explain Tables and Fields which we will be use...


          FieldName                                       TableName

1)       SECTOR                                           ContributionCategory
2)       End Weight(Benchmark)                   AllMesure

3)       Total Return Contribution                AllMesure

4)       Security Name                                 Sect




and I have Tried with this example , But I'm Not getting as I expected


TestFields =
var sectorName = CALCULATE(
    MAX(ContributionCategory[SECTOR]),
    FILTER (
        VALUES ( ContributionCategory[SECTOR] ),
        RANKX ( ALL ( ContributionCategory[SECTOR] ), AllMeasure[Total Return Contribution],, DESC ) = 1
    )
)
var data =
CALCULATE(
    MAX(Sect[Security Name]),
    FILTER(ContributionCategory,ContributionCategory[SECTOR] = sectorName),
    FILTER (
        VALUES ( Sect[Security Name] ),
        RANKX ( ALLNOBLANKROW ( Sect[Security Name] ), AllMeasure[Total Return Contribution],, DESC ) = 1
    )
)

return data;


using this example I'm getting blank value on first Rank
and when I tried with below RANKX Functions in data variable
RANKX ( ALLNOBLANKROW ( Sect[Security Name] ), AllMeasure[Total Return Contribution],, DESC ) = 2
then I'm getting security name, so I want to Ignore or filter all the blank or NonText value From first rank ,and want 2 rank value on first ;
Thanks in advance

Community Support Team
Community Support Team

Re: DAX RANKX Function filter

Hi @kjshah5030,

 

Based on my test, we can create the measure to meet your requirement.

 

Measure 3 = IF(ISBLANK([Total Return Contribution]),BLANK(),RANKX(ALLSELECTED(ContributionCategory),[Total Return Contribution],,DESC,Dense))
TestFields111 = 
var sectorName = CALCULATE(
    MAX(ContributionCategory[SECTOR]),
    FILTER (
        VALUES ( ContributionCategory[SECTOR] ),
        [Measure 3]=1
    )
)
var data =
CALCULATE(
    MAX(Sect[Security Name]),
    FILTER(ContributionCategory,ContributionCategory[SECTOR] = sectorName),
    FILTER (
        VALUES ( Sect[Security Name] ),
        [Measure 3]=1
    )
)
return data

Here is the result for your reference.

 

Capture.PNG

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/ory81yxn5538qi6/Cyborg%20Equity%2018Q1%20_changeWithShum1.pbix?dl=0

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kjshah5030 Frequent Visitor
Frequent Visitor

Re: DAX RANKX Function filter

Hi @v-frfei-msft
Thanks for Kind reply ,
But I'm not getting expected result ,
I want to filter all the 'Financial'(means first rank sector) sector security name (Not all security) and want that security name which AllMeasure[Total Return Contribution] is high
Means In this case
may be sicurity name will came "DBS Group Holding Ltd"

can you please see once more time 
Thanks Again for helping me

Community Support Team
Community Support Team

Re: DAX RANKX Function filter

Hi @kjshah5030,

 

Again I have a try to add the measures as below to meet your requirement.

 

Measure 3 = IF(ISBLANK([Total Return Contribution]),BLANK(),RANKX(ALLSELECTED(ContributionCategory),[Total Return Contribution],,DESC,Dense))
Measure 4 = IF(ISBLANK([Total Return Contribution]),BLANK(),RANKX(ALLNOBLANKROW(Sect[Security Name]),[Total Return Contribution],,DESC,Dense))
TestFields111 = 
var sectorName = CALCULATE(
    MAX(ContributionCategory[SECTOR]),
    FILTER (
        VALUES ( ContributionCategory[SECTOR] ),
        [Measure 3]=1
    )
)
var data =
CALCULATE(
    MAX(Sect[Security Name]),
    FILTER(ContributionCategory,ContributionCategory[SECTOR] = sectorName),
    FILTER (
        VALUES ( Sect[Security Name] ),
        [Measure 4]=1
    )
)
return data

Here is the result I got, Seemed the result should be Constellation Software Inc.

 

2212.PNG

 

For more details, please check the pbix as attached.

 

https://www.dropbox.com/s/ory81yxn5538qi6/Cyborg%20Equity%2018Q1%20_changeWithShum1.pbix?dl=0

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kjshah5030 Frequent Visitor
Frequent Visitor

Re: DAX RANKX Function filter


Hi @v-frfei-msft

I'm very sorry for late reply , I was busy in something else , so sorry for that
and about above result sorry but i'm not getting expected result 
let see in ex... , there is a sector dropdown and account dropdown , select value like  "Mawer International equity fund" from accout dropdown and from sector "Financials" , then u can see in your result "DBS Group Holdings Ltd" I want this name on first rank 
but you have to assume that we dose not have sector dropdown . 


Thanks,

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 426 members 4,109 guests
Please welcome our newest community members: