DAX Compare Credit Score from one table to Min Credit Score and Max Credit Score from another table

[drintharamy]

Hi Power BI community,

I have a Borrower Credit Score from one table (1st table).

I also have Minimum Credit Score, Maximum Credit Score and Tier from another table (2nd table).

I would like to create a DAX formula to get a Tier from 2nd table by checking the Borrower Credit Score from one table (1st table) to Minimum Credit Score and Maximum Credit Score to get Tier from another table (2nd table).

Example:

If Borrower Credit Score is >= CreditScoreMin and <= CreditScoreMax, then give me Tier.

Sample data from 1st table:

Name                      Credit Score

John Smith              770

Jane Doe                 670

Sample data from 2nd table:

Tier CreditScoreMin       CreditScoreMax
1     765                          850
2     714                          764
3     675                          713
4     650                          674
5     0                              649

In the sample data for John Smith, his Tier would be 1 because his credit score is between 765 and 850.

In the sample data for Jane Doe, her Tier would be 4 because her credit score is between 650 and 674.

I am trying to create a DAX formula to accomplish but I haven't been able to.

Thank you for your help.

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula in Table1

=CALCULATE(SUM(Table2[Tier]),FILTER(Table2,Table2[CreditScoreMin]<=[Credit Score]&&Table2[CreditScoreMax]>=[Credit Score]))

Hope this helps.

[v-frfei-msft · Accepted solution]

Hi @drintharamy ,

Two ways to achieve that.

Measure: 

Tier of name = 
VAR cred =
    MAX ( 'name'[ Credit Score] )
RETURN
    CALCULATE (
        MAX ( Tier[Tier] ),
        FILTER ( Tier, Tier[CreditScoreMin] < cred && Tier[CreditScoreMax] > cred )
    )

Calculated column: 

Tier_name = 
VAR cred = 'name'[ Credit Score]
RETURN
    CALCULATE (
        MAX ( Tier[Tier] ),
        FILTER ( Tier, Tier[CreditScoreMin] < cred && Tier[CreditScoreMax] > cred )
    )

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula in Table1

=CALCULATE(SUM(Table2[Tier]),FILTER(Table2,Table2[CreditScoreMin]<=[Credit Score]&&Table2[CreditScoreMax]>=[Credit Score]))

Hope this helps.

[Nathaniel_C]

@drintharamy 

Try this:
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

Credit Tier = 
var Score = MAX(table1[Credit Score])

var calc = SWITCH(TRUE(),
                  Score >=765, 1,
                  Score >=714, 2,
                  Score >=675, 3,
                  Score >=650, 4,
                  Score >=0, 5)
return calc