Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Assign value based on key

Hi,   Complete newbie in Power BI here, so please bear with me. Many thanks in advance!!!   What I am trying to achieve? determine the donor status based on 2 criterias: A donor is inactive Dat...
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anonymous 

    Here are the steps you can refer to :

    (1)This is my test data :

    (2)You can create a calculated column :

    Donor_Status = var _current_id='amount_donated_last_date'[donorID]
var _current_date = 'amount_donated_last_date'[date]
var _sum =SUMX(FILTER('amount_donated_last_date','amount_donated_last_date'[donorID]=_current_id && amount_donated_last_date[date] = _current_date),[Total])

return
IF(YEAR('amount_donated_last_date'[date]) >2012 && _sum >5 , "Active","Inactive")

    (3)Then we can meet your need , the result is as follows:

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

FreemanZ's avatar
FreemanZ
Super User
3 years ago

Try to add the new column with the code below:

 

Donor_Status=
VAR LastYear = 
CALCULATE(
     YEAR(MAX(amount_donated_last_date[date])),
    ALLEXCEPT(amount_donated_last_date, amount_donated_last_date[donorID])
)
VAR TotalSum =
CALCULATE(
     SUM(amount_donated_last_date[Total]),
    ALLEXCEPT(amount_donated_last_date, amount_donated_last_date[donorID])
)
RETURN 
IF ( LastYear >=2012  && TotalSum>5, "Active", "Inactive" )
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks a lot for your contribution!

 

It doesn't seem to be working unfornutaly. For donorID 100005 yes, but not for 100003 as you can see on the screenshots below. 100003 should be inactive (all donations in 2013 are below 5 USD).

 

 

 

 

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi , Anonymous 

    Here are the steps you can refer to :

    (1)This is my test data :

    (2)You can create a calculated column :

    Donor_Status = var _current_id='amount_donated_last_date'[donorID]
var _current_date = 'amount_donated_last_date'[date]
var _sum =SUMX(FILTER('amount_donated_last_date','amount_donated_last_date'[donorID]=_current_id && amount_donated_last_date[date] = _current_date),[Total])

return
IF(YEAR('amount_donated_last_date'[date]) >2012 && _sum >5 , "Active","Inactive")

    (3)Then we can meet your need , the result is as follows:

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    aha, you wanna check the max donation, try this:

     

    Donor_Status=
    VAR LastYear =
    CALCULATE(
         YEAR(MAX(amount_donated_last_date[date])),
        ALLEXCEPT(amount_donated_last_date, amount_donated_last_date[donorID])
    )
    VAR MaxAmount=
    CALCULATE(
         MAX(amount_donated_last_date[Total]),
        ALLEXCEPT(amount_donated_last_date, amount_donated_last_date[donorID])
    )
    RETURN
    IF ( LastYear >=2012  && TotalSum>5, "Active", "Inactive" )