Forum Discussion

Diptarup's avatar
Diptarup
Helper II
5 years ago

Dax Calculation

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 f...
Show and Tell
v-henryk-mstf's avatar
v-henryk-mstf
Community Support
5 years ago

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's avatar
    Diptarup
    Helper II
    5 years ago

    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's avatar
      v-henryk-mstf
      Community Support
      5 years ago

      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.