Re: Dax Calculation

Diptarup · ‎04-13-2021

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

v-henryk-mstf · ‎04-14-2021

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.

Diptarup · ‎04-17-2021

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

v-henryk-mstf · ‎04-18-2021

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.

Jihwan_Kim · ‎04-13-2021

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.

Go to My LinkedIn Page

Diptarup · ‎04-22-2021

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.

selimovd · ‎04-13-2021

Hey @Diptarup ,

check the following SQLBI article, there they are describing exactly the scenario you are dealing with:

Displaying Nth Element in DAX - SQLBI

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

Diptarup · ‎04-22-2021

Hi Selimovd,

Many thanks for your reply.