topic Re: Select a Table value based on a DAX function in DAX Commands and Tips

Select a Table value based on a DAX function

driplytics — Fri, 10 Jun 2022 20:36:05 GMT

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.

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

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.

Re: Select a Table value based on a DAX function

vapid128 — Fri, 10 Jun 2022 22:49:34 GMT

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]))

Re: Select a Table value based on a DAX function

Jihwan_Kim — Sat, 11 Jun 2022 02:39:49 GMT

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] )

Re: Select a Table value based on a DAX function

driplytics — Sat, 11 Jun 2022 03:47:42 GMT

This is the Solution !!!

it worked.

Thanks for Helping Out.