New table from 2 tables

[BTI_Afuentes]

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 calculated table with score results from projects. (Projects could have more that one score with different dates)

I need a new table with all data in "Scores and Probability" table and with date and project id columns from "Project scores table".

(Below is an example of table and this is a link for the pbix file Pbix file )

This would be an example of new table needed:

EX-Prob	EX-Score	Date	ProjectID
99.9827%	0
99.9795%	1
99.9756%	2
99.9710%	3
99.9655%	4
99.9590%	5
...	...
0.6527%	79	01/01/2018	8000435
0.6527%	79	01/01/2019	8000435
0.5494%	80
0.4624%	81
0.3891%	82
0.3274%	83
0.2755%	84	01/01/2017	8000435
...	...
0.0173%	100

This is an example of Project scores table (table BTI_Probability of default on model)

nadb_projectid_opp	Date	Sum of Credit Scoring	Prob Of Default
8000435	01/01/2017	84	0.28%
8000435	01/01/2018	79	0.65%
8000435	01/01/2019	79	0.65%

Scores and Probability (table PIat100 in model)

The numbers goes from 0 to 100 consecutively, but I cut them by size issue in the post.

EX-Prob	EX-Score
99.9827%	0
99.9795%	1
99.9756%	2
99.9710%	3
99.9655%	4
99.9590%	5
99.9512%	6
99.9420%	7
99.9310%	8
99.9179%	9
99.9024%	10
...	...
99.8621%	12
99.8360%	13
99.8051%	14
99.7683%	15
99.3473%	21
99.2248%	22
99.0795%	23
98.9072%	24
...	...
2.1619%	72
1.8242%	73
1.5385%	74
1.2969%	75
1.0928%	76
0.9205%	77
0.7752%	78
0.6527%	79
0.5494%	80
0.4624%	81
0.3891%	82
0.3274%	83
0.2755%	84
0.2317%	85
0.0244%	98
0.0205%	99
0.0173%	100

Thanks, I really appreciate your help.

[Anonymous]

It sounds like what you want to do is some kind of join. Joins have a few forms -- Left, Right, Full, and Inner. It sounds like you want a Left Join, with the table of all possible scores left joined to the table of project scores. What you need is at least one matching column between those tables - in this case, it seems like the % columns are the basis of the join, or your matching column. That means when the values in that column match, it will append the value from the other table to that row. 

There are a few ways to do this kind of join, including using the Relationships tool in PowerBI. But it sounds like you want a full table that mixes these values, so I would use the "Merge Queries" tool in the Power Query Editor (right click a listed query to "Edit Query" for any of your qureies > Home tab > "Merge Queries" in the "Combine" section . There, you will select the two tables you want to join together, identify which column to join by, and identify the Join Kind. When you click "Ok", you will get a new query with both tables joined together. If you realize that the join you chose wasn't what you wanted, you can always edit that query to change the type of Merge.


If you only need this table for display purposes in your report, then you should just add a relationship between these tables. When you build a table with columsn from both tables then, it will display as you are requestion. There is a lot of information out there on relationships in Power BI. https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships

[BTI_Afuentes]

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 · Accepted solution]

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:

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.

https://docs.microsoft.com/en-us/dax/format-function-dax

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]

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