Forum Discussion

Frequent Visitor

8 years ago

Solved

Dax Top 1 Name

Hello Everyone, I am a new to Power BI, I need a help for Dax , I have matrix table , table image is given below so , I want to top 1 GICS Level name using High Banchmark End Weight , wan...

Zubair_Muhammad

8 years ago

Try with

=
CALCULATE (
    MAX ( [GICS Level] ),
    FILTER (
        VALUES ( ContributionCategory[SECTOR] ),
        RANKX ( ALL ( ContributionCategory[SECTOR] ), [EndWeightBanchmark],, DESC ) = 1
    )
)

Zubair_Muhammad

Icon for Community Champion rank

Community Champion

8 years ago

I think you will need to modify the table parameter of RANKX

But not sure

RANKX(CALCULATETABLE(VALUES(ContributionCategory[SECTOR]),[VALUES]<>BLANK())

Frequent Visitor

8 years ago

Hi,
Zubair_MuhammadThanks for kind reply,
I tried with

here my fields and table are different but scenario is same , I m not getting value on first
rank , getting blank value on first rank
any other solution ?

Zubair_Muhammad
Community Champion
8 years ago
kjshah5030

Could you copy paste some sample data with expected results?
- kjshah5030
  Frequent Visitor
  8 years ago
  here is code , which I have tried ,u can see
  
  var first = CALCULATE(
      MAX( Sect[Security Name]),
      FILTER(
          VALUES ( ContributionCategory ),
          ContributionCategory[SECTOR] = thiredPortfolio
      ),
      FILTER(Sect,
          Sect[Security Name] <> BLANK()
      ),
      FILTER (
          VALUES( Sect[Security Name] ),
          RANKX ( ALLNOBLANKROW ( Sect[Security Name] ), AllMeasure[Total Return Contribution],, DESC ) = 1
      )
  )
  
  Here , I m trying to get top 1 with ignoring blank or empty
  when I tried with RANKX ( ALLNOBLANKROW ( Sect[Security Name] ), AllMeasure[Total Return Contribution],, DESC ) = 6
  then I got my expected result but this is not perfact , bacause all the 1 to 5 ranks results is blank or empty ,
  so i want ignore all five ... and ,
  always I want my result in first rank(ignoring all blank or empty values)
- kjshah5030
  Frequent Visitor
  8 years ago
  Hi
  Zubair_Muhammad
  I found one more solution , I have recently checked the value, is not blank but it is nontext value.
  so now I want to use ISNONTEXT() for check whether it is text or not. I want only text value rank by desc ,
  so how can I use ISNONTEXT() in RANKX() ?
  I tried but not getting proper result. so can you please help one more time?

Related content