Forum Discussion

BTI_Afuentes's avatar
BTI_Afuentes
Icon for Helper I rankHelper I
6 years ago
Solved

New table from 2 tables

Hi everyone, I need your help for creating a table with dax and can create a chart with new table. I have two tables: One table contains two columns with Scores and Probability. Another table is a...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    Hello @BTI_Afuentes

    You can use the NATURALLEFTOUTERJOIN function to create a new table to get it.

    https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/#code7

    Formula:

    New table =
VAR A =
    SELECTCOLUMNS (
        PIat100,
        "EX-Prob", PIat100[EX-Probabilitylist] + 0,
        "EX-Score", PIat100[EX-CreditScorelist]
    )
VAR B =
    SELECTCOLUMNS (
        BTI_ProbabilityofDefault,
        "EX-Prob", BTI_ProbabilityofDefault[Prob Of Default] + 0,
        "Date", BTI_ProbabilityofDefault[Date],
        "ProjectID", BTI_ProbabilityofDefault[KeyPI100]
    )
RETURN
    NATURALLEFTOUTERJOIN ( A, B )

    Result:

    3.JPG

    by the way, for your case, please don't format[Default Prob] in your summary table,

    "Prob Of Default", Format((1/(1+EXP(-(((sumx(values(nadb_creditriskstatuses[_nadb_riskindicator_value]),
    calculate(max(nadb_creditriskstatuses[nadb_statuspoints2])))) /2)-(sum(nadb_projectriskratings[nadb_statuspoints_porjrr])))
    *(LN(4)/8))))))))),"Percentage"),
    This will lead to this column is a text formula not a number.
    Converts a value to text according to the specified format.

    and here is the sample pbix file, please try it.

    Best regards

    Lin

BTI_Afuentes's avatar
BTI_Afuentes
Icon for Helper I rankHelper I
6 years ago

Hi, thank you for your response.

 

Project score is a summarized table, so I think using merge in Power Quiery is not an option, right? I'm thinking on creating a join but in dax.. Do you have any idea of function that I have to used or if that is possible?

 

Thanks,

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
6 years ago

Hello @BTI_Afuentes

You can use the NATURALLEFTOUTERJOIN function to create a new table to get it.

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/#code7

Formula:

New table =
VAR A =
    SELECTCOLUMNS (
        PIat100,
        "EX-Prob", PIat100[EX-Probabilitylist] + 0,
        "EX-Score", PIat100[EX-CreditScorelist]
    )
VAR B =
    SELECTCOLUMNS (
        BTI_ProbabilityofDefault,
        "EX-Prob", BTI_ProbabilityofDefault[Prob Of Default] + 0,
        "Date", BTI_ProbabilityofDefault[Date],
        "ProjectID", BTI_ProbabilityofDefault[KeyPI100]
    )
RETURN
    NATURALLEFTOUTERJOIN ( A, B )

Result:

3.JPG

by the way, for your case, please don't format[Default Prob] in your summary table,

"Prob Of Default", Format((1/(1+EXP(-(((sumx(values(nadb_creditriskstatuses[_nadb_riskindicator_value]),
calculate(max(nadb_creditriskstatuses[nadb_statuspoints2])))) /2)-(sum(nadb_projectriskratings[nadb_statuspoints_porjrr])))
*(LN(4)/8))))))))),"Percentage"),
This will lead to this column is a text formula not a number.
Converts a value to text according to the specified format.

and here is the sample pbix file, please try it.

Best regards

Lin

  • BTI_Afuentes's avatar
    BTI_Afuentes
    Icon for Helper I rankHelper I
    6 years ago

    Thank you! for the solution and suggestions.  That worked! as expected.