Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Add dimension within addcolumn union

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:

CompanyTypeTeam
ASales 
BSales 
ACosts1
BCosts1
ACosts2
BCosts2
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.

      CompanyTypeTeam
      ASales 
      BSales 
      ACosts1
      BCosts2

       

       

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

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

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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 😉