Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am new to the PowerBi and would require assistance to solve a problem. I want to get the value of the product A & B based on the second highest year. As shown in the below table ,I want to have a formula which will give me the value of 17 for product A in 2019 & 16 for product B, as 2019 is the 2nd highest year in the table.
Please note that Year is not fixed and there are instance for other products werein the year may starts from 2018.
Year | Product | Activity |
2020 | A | 15 |
2019 | A | 17 |
2018 | A | 13 |
2017 | A | 12 |
2020 | B | 19 |
2019 | B | 16 |
2018 | B | 21 |
2017 | B | 15 |
Hi @Diptarup ,
I agree with @Jihwan_Kim solution. But I also did the following tests, hoping to expand your ideas:
M =
VAR a =
RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[Year] ) ),, DESC, DENSE )
RETURN
CALCULATE (
SUM ( 'Table'[Activity] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Year] ),
a = 2
&& 'Table'[Column] = MAX ( 'Table'[Column] )
)
)
Here is the sample pbix file.
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can you please share the calculation for the col "Column" as the pbix file that you have acttcahed i am unable to open it due to some issue
Hi @Diptarup ,
The calculated column is below:
Column = IF('Table'[Product]="A",1,2)
Best regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Diptarup
Please check the below picture and the calculated measure, whether it is what you are looking for.
Ranktwo Activity =
IF (
RANKX (
ALLSELECTED ( Products[Year] ),
CALCULATE ( MAX ( Products[Year] ) ),
,
DESC
) = 2,
SUM ( Products[Activity] ),
BLANK ()
)
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/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi Jihwan,
I have tried the option that you have said, but not getting the desired result. I am re-phrasing the question, hoping to be clear...
I need to show the lowest value of an activity based on the most recent assesment. Please refer to the below table.
Year Activity Value
1/1/2020 A 3
1/1/2018 A 5
1/1/2017 A 2
1/1/2021 B 1
1/1/2021 C 15
1/1/2020 C 2
1/1/2019 C 6
1/1/ 2018 D 2
1/1/2017 D 16
1/1/2016 D 1
1/1/2017 E 4
1/1/2021 f 19
1/1/2020 G 12
1/1/2021 H 1
I am expecting the output for the lowest values as D -2 (2018), B - 1 (2021), A - 3 (2020) , E - 4 (2017), H - 1 (2021). Please remember the values should be based on the latest assessment for each activity. Any help will be highly appreciated.
Hey @Diptarup ,
check the following SQLBI article, there they are describing exactly the scenario you are dealing with:
Displaying Nth Element in DAX - SQLBI
Hi Selimovd,
Many thanks for your reply.
I have tried the option that you have said, but not getting the desired result. I am re-phrasing the question, hoping to be clear...
I need to show the lowest value of an activity based on the most recent assesment. Please refer to the below table.
Year Activity Value
1/1/2020 A 3
1/1/2018 A 5
1/1/2017 A 2
1/1/2021 B 1
1/1/2021 C 15
1/1/2020 C 2
1/1/2019 C 6
1/1/ 2018 D 2
1/1/2017 D 16
1/1/2016 D 1
1/1/2017 E 4
1/1/2021 f 19
1/1/2020 G 12
1/1/2021 H 1
I am expecting the output for the lowest values as D -2 (2018), B - 1 (2021), A - 3 (2020) , E - 4 (2017), H - 1 (2021). Please remember the values should be based on the latest assessment for each activity. Any help will be highly appreciated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |