Dax Help!!

[Anonymous]

Hi,

I'm looking for a DAX formula which can help me get the below mentioned results.

Account Name	Country	Revenue
XYZ	Singapore	1000	Ignore This
XYZ	Thailand	800	Ignore This
XYZ	Vietnam	900	Ignore This
XYZ	Sri Lanka	1200	Consider this
ABC	Singapore	400	Consider this
DEF	Philippines	200	Consider this

For example, there's an account with multiple engagements in different locations. I should consider the account with highest revenue in a given location and ignore the rest.

Any help on this would be really appreciated!!

Thanks in advance!

Regards,

Mahesh

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below measure.

Result Measure =
VAR currentaccount =
MAX ( 'Table'[Account Name] )
VAR maxrevbyacount =
GROUPBY (
FILTER ( ALL ( 'Table' ), 'Table'[Account Name] = currentaccount ),
'Table'[Account Name],
"@maxrev", MAXX ( CURRENTGROUP (), 'Table'[Revenue] )
)
RETURN
IF (
SUM ( 'Table'[Revenue] ) = MAXX ( maxrevbyacount, [@maxrev] ),
"Consider this",
"Ignore This"
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

hi @Jihwan_Kim ,

Thank you for responding so quickly.

I'm looking for the result as below:

Account Name	Country	Revenue	Country_Result
XYZ	Singapore	1000
XYZ	Thailand	800
XYZ	Vietnam	900
XYZ	Sri Lanka	1200	Sri Lanka
ABC	Singapore	400	Singapore
DEF	Philippines	200	Philippines

If the the critiria isn't matching, I should get a blank else the Country name.

[amitchandak]

@Anonymous , Try like

new column =
var _max = maxx(filter(Table, [Account Name] = earlier([Account Name])),[Revenue])
return
if( [Revenue] =_max, "Consider this", "Ignore This")

[Anonymous]

Hi @amitchandak ,

Thank you for your swift response.

I'm looking for the result as below:

Account Name	Country	Revenue	Country_Result
XYZ	Singapore	1000
XYZ	Thailand	800
XYZ	Vietnam	900
XYZ	Sri Lanka	1200	Sri Lanka
ABC	Singapore	400	Singapore
DEF	Philippines	200	Philippines

If the the critiria isn't matching, I should get a blank else the Country name.

[amitchandak · Accepted solution]

@Anonymous ,

new column =
var _max = maxx(filter(Table, [Account Name] = earlier([Account Name])),[Revenue])
return
if( [Revenue] =_max, [Country],blank())