topic Re: Get month value where rank = 1 from another Table in DAX Commands and Tips

Get month value where rank = 1 from another Table

ankurshah01 — Wed, 20 Dec 2023 16:58:06 GMT

Hi All,

I have a table that shows below data.

Table A

Calmonth	Rank1	Volume
Jan-23	4	40
Feb-23	1	50
Mar-23	2	45
Apr-23	6	34
May-23	9	20
Jun-23	8	25
Jul-23	5	36
Aug-23	7	30
Sep-23	10	10
Oct-23	11	8
Nov-23	3	42

Rank1 is a measure.

Now based on above table, i need to get the Calmonth value where Rank1 = 1 in Table B.

So in this case i will get Feb 2023.

I created a new column in Table B & tried using Calculate with max & filter (where rank = 1) but it returns always Nov 2023.

New Column = CALCULATE(max('Table A'[Calmonth]),FILTER('Table A', 'Table A'[Rank1] = 1))

I tried with different options using Lookup or selectedvalue but not getting required output.

how to resolve this issue?

Regards

Ankur

Re: Get month value where rank = 1 from another Table

Dangar332 — Wed, 20 Dec 2023 19:25:36 GMT

HI, ankurshah01

try below code for column

New Column = CALCULATE( 'Table A'[Calmonth], FILTER( all('Table A'[rank]), 'Table A'[Rank1] = 1 ) )

Re: Get month value where rank = 1 from another Table

ankurshah01 — Wed, 20 Dec 2023 19:36:26 GMT

Hi,

Above code is not working since, after calculate there needs to be an expression.

Regards

Ankur

Re: Get month value where rank = 1 from another Table

Dangar332 — Wed, 20 Dec 2023 19:40:42 GMT

Hi, ankurshah01

Can you clarify What problem you face?

Re: Get month value where rank = 1 from another Table

ankurshah01 — Wed, 20 Dec 2023 19:50:02 GMT

Hi Dangar332 ,

CALCULATE(

'Table A'[Calmonth],

FILTER(

I am not able to input above highlighted table & field after calculate.

It is only showing me Rank & Volume fields.

Regards

Ankur

Re: Get month value where rank = 1 from another Table

Dangar332 — Wed, 20 Dec 2023 20:14:55 GMT

Hi, ankurshah01

ankurshah01

New Column =

Var a = 'Table A'[Calmonth]

Return

CALCULATE( a,

'Table A'[Rank1] = 1

)

New column=

Minx(

FILTER(

'Table A',

'Table A'[Rank1] = 1

'Table A'[Calmonth]

)

Re: Get month value where rank = 1 from another Table

ankurshah01 — Wed, 20 Dec 2023 20:47:41 GMT

Hi Dangar332 ,

New Column =

Var a = 'Table A'[Calmonth]

Return

CALCULATE( a,

'Table A'[Rank1] = 1

)

Above code is giving me -- >>

A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Below code is giving me value as Jan 2023 as output --

New column=

Minx(

FILTER(

'Table A',

'Table A'[Rank1] = 1

'Table A'[Calmonth]

)

Regards

Ankur

Re: Get month value where rank = 1 from another Table

Dangar332 — Thu, 21 Dec 2023 06:09:14 GMT

Hi, ankurshah01

kindly update your
my rank measure is below

Measure = RANK( DENSE, ALL('Table'[volume]), ORDERBY('Table'[volume],DESC) )

and

column dax is below

Column = CALCULATE( MIN('Table'[calmonth]), FILTER('Table',[Measure]=1) )

above is work for me
when i use rankx() instead of Rank() i got error like circular dependency detect

rank1 = RANKX(ALL('Table'[volume]),'Table'[volume],MIN('Table'[volume]))

so update your rank code if it made from rankx()