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
kjshah5030
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
v-frfei-msft
Community Support
Community Support

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 others find it more quickly.

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

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 others find it more quickly.

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

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 others find it more quickly.


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