Add dimension within addcolumn union

[Anonymous]

Hello!

I made 2 manual dimension tables, named 'Company' and 'Teams'.

Also made a calculated table as shown below. With this table I'm able to merge 'Sales' and 'Costs' within the same table.

But, for 'Costs' I would like to a column as subdimension 'Teams'. Unfortunatly, can't make this work 🤦‍.

I would like to achieve this without using relationships between tables.

Hopefully this makes sense and somebody is able to help me out! Thanks a lot.

Facts = 
UNION(
    ADDCOLUMNS(
        'Company';
        "Type"; "Sales";
        "Team"; "";
        "TestMeasure"; ... 
    );
        ADDCOLUMNS(
        'Company';
        "Type"; "Costs";
        "Team"; "🤦‍";
        "TestMeasure"; ...
))

Desired result:

Company	Type	Team
A	Sales
B	Sales
A	Costs	1
B	Costs	1
A	Costs	2
B	Costs	2

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

You could use this formula as below:

Table = 
UNION(
    ADDCOLUMNS(
        'Company',
        "Team", "",
        "Type", "Sales",
        "TestMeasure", TODAY()
    ),
        ADDCOLUMNS(
        GENERATE('Company',FILTER(Team,Team[Team]<>3)),
        "Type", "Costs",
        
        "TestMeasure", NOW()
))

Result:

Regards,

Lin

[Anonymous]

Thanks a lot!

The application of the generate function is what I was looking for. 

You even filtered out team 3 from my example to get the desired result 😉

[amitchandak]

@Anonymous , It should work by giving column name like

Facts = 
UNION(
    selectCOLUMNS(Table1,"Company";
        Table[Company];
        "Type"; "Sales";
        "Team"; "";
        "TestMeasure"; ... 
    );
        selectCOLUMNS(Table1,"Company";
        Table[Company];
        "Type"; "Costs";
        "Team"; Table[Team];
        "TestMeasure"; ...
))

[Anonymous]

@amitchandak thanks for your reply!

Maybe im missing something, but addcolumns expects an expression. Table[team] is not an expression.

I tried a lot of different expressions, but I'm not able to find the correct one.

The best one I found was RELATED('Team'[Team]). Unfortunatly this gave the result below.

Company	Type	Team
A	Sales
B	Sales
A	Costs	1
B	Costs	2

[amitchandak]

@Anonymous , can you share source data. You have already shared desired output

[Anonymous]

https://drive.google.com/open?id=1j9UOmy1cjbrjXREJSoY-_elkrwxj2ACX 

If a relation between tables is necessary, feel free to suggest.