Forum Discussion

driplytics's avatar
driplytics
Frequent Visitor
4 years ago
Solved

Select a Table value based on a DAX function

Hey,

i have GDP data with colunms : Country, GDP, Year.

 

i used a Dax functions to calculate GDP Growth %, and ranked it In the Growth Rank Column.

Here's what i want to do :

I want to show the Year of highest Growth %  in a Card Visual (from the Table, it shows 1981), but can't seem to know how best to do it.

 

This is a table visual i created on power bi.

 

Table Visual

 

 

 

 

 

 

 

 

 

Apart from Year and GDP Column every other Column is calculated using DAX.

 

Here are the formulas :

 

LY GDP =
var _Ly = LASTNONBLANK('Countries GDP 1960-2020'[Year],1) - 1
var _1 = CALCULATE([GDPc], ALL('Countries GDP 1960-2020'[Year]),
 'Countries GDP 1960-2020'[Year] = _Ly )
return
_1
 
Growth % = DIVIDE((sum([GDP]) - [LY GDP] ),[LY GDP])
 
Growth Rank = RANKX ( ALL ( 'Countries GDP 1960-2020'[Year] ),[Growth %] )
 
I have Gotten tremendious support from here, and i appreciate your responses.
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure how your data model looks like, but please try to put the below measure into a card visualization.

     

    expected measure for card visual: =
SUMMARIZE (
    FILTER (
        ADDCOLUMNS (
            ALL ( 'Countries GDP 1960-2020'[Year] ),
            "@ranking", [Growth Rank]
        ),
        [@ranking] = 1
    ),
    'Countries GDP 1960-2020'[Year]
)

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure how your data model looks like, but please try to put the below measure into a card visualization.

     

    expected measure for card visual: =
SUMMARIZE (
    FILTER (
        ADDCOLUMNS (
            ALL ( 'Countries GDP 1960-2020'[Year] ),
            "@ranking", [Growth Rank]
        ),
        [@ranking] = 1
    ),
    'Countries GDP 1960-2020'[Year]
)
    • driplytics's avatar
      driplytics
      Frequent Visitor
      4 years ago

      This is the Solution !!!

      it worked. 

      Thanks for Helping Out.

  • vapid128's avatar
    vapid128
    Solution Specialist
    4 years ago

    add colnum

    LastYearGDP = lookupvalue('Countries GDP 1960-2020'[GDP], 'Countries GDP 1960-2020'[Year], 'Countries GDP 1960-2020'[Year] - 1, 'Countries GDP 1960-2020'[Country], 'Countries GDP 1960-2020'[Country])

     

    add measure

    BestGDP = maxx(values( 'Countries GDP 1960-2020'[Year]), calculate(DIVIDE(sum('Countries GDP 1960-2020'[GDP])-sum('Countries GDP 1960-2020'[LastYearGDP]),sum('Countries GDP 1960-2020'[LastYearGDP]))