Create new table from two tables via DAX or M

[Dataman9000]

Hi,

I have a composite model with a lot of tables.  I need to create a new fact table from a list of tables I have via Dax or M.  Below is a an example of my table structure, can someone please provide the basic codes to get me started?

Fact Table

Account	Cost Center	Value
1	100	40
2	100	27
3	100	57
4	100	68
5	100	4
6	100	50
1	200	52
2	200	63
3	200	25
4	200	13
5	200	33
6	200	27

Dim Table

Account	Group1	Group2
1	a	x
2	a	x
3	a	x
4	b	x
5	b	y
6	b	y

New Table based on conditions:  Combine Fact table with Dim table when Dim table Group1 is "a".

Account	Cost Centr	Value	Group1	Group2
1	100	40	a	x
2	100	27	a	x
3	100	57	a	x
1	200	52	a	x
2	200	63	a	x
3	200	25	a	x

Thank you in advance!

[smpa01 · Accepted solution]

@Dataman9000  you can do this

GENERATE('fact', var one = 'fact'[Account] return SELECTCOLUMNS(FILTER(dim, dim[Group1]="a"&&dim[Account]=one),[Group1],[Group2]))

[Dataman9000]

TY - this is the solution I am looking for.

[ray_aramburo]

Then you will need to provide a SQL-like query where you create the join and specify the group with a Where clause.

[Dataman9000]

I don't have access to the source, that's why I need to do it using Dax or M.

[ray_aramburo]

1. Create a duplicate/reference of the Dim Table and filter it for group 1 = "a"

2. Go to fact table, merge queries and select account columns, and click Ok:

3. Expand to extract Group 1 and Group 2 columns

4. You're done 🙂

If you only need for Group a data to be on the fact table, then dismiss step 1 and filter out after you have merged as Step 4.

You can see my file as example and leverage from it.

[Dataman9000]

Hi Ray,

Thank you for your response, but I need a solution from "Creat table".  I am using a SSAS cube, so those options are not availble in the model.