Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
I tried with different options using Lookup or selectedvalue but not getting required output.
how to resolve this issue?
Regards
Ankur
Solved! Go to 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()
HI, @ankurshah01
try below code for column
New Column =
CALCULATE(
'Table A'[Calmonth],
FILTER(
all('Table A'[rank]),
'Table A'[Rank1] = 1
)
)
Hi,
Above code is not working since, after calculate there needs to be an expression.
Regards
Ankur
Hi @Dangar332 ,
Regards
Ankur
Hi, @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]
)
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
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()
User | Count |
---|---|
57 | |
21 | |
19 | |
18 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |