Get month value where rank = 1 from another Table

[ankurshah01]

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

[Dangar332]

HI, @ankurshah01 

try below code for column

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

[ankurshah01]

Hi, 

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

Regards

Ankur

[Dangar332]

Hi, @ankurshah01 

Can you clarify What problem you face?

[ankurshah01]

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

[Dangar332]

Hi, @ankurshah01 

@ankurshah01 

New Column = 

Var a = 'Table A'[Calmonth]

Return 

  CALCULATE( a,

           'Table A'[Rank1] = 1

       )

Or 

New column=

Minx(

       FILTER(

           'Table A',

           'Table A'[Rank1] = 1

       ),

 'Table A'[Calmonth]

)

[ankurshah01]

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

[Dangar332 · Accepted solution]

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