Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX unpivot

Hi    In the DAX formula I would like to have the effect of a column that identify the "transaction"/accounting type. the column would contain eg Gross UPR movement, GrossPremium and RIIBNRMove ...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    5 years ago

    HI  Anonymous 

    You could try this formula:

    Table = 
UNION( 
SELECTCOLUMNS(FACT_Sales,"ProductName",RELATED(DIM_Product[ProductName]),"AccPeriodMonth",RELATED(DIM_AccountingPeriod[AccPeriodMonth]),"Companyname",RELATED(DIM_Company[CompanyName]),"TransactionType","Sales","Amount",[SalesAmount]),

SELECTCOLUMNS(FACT_CostOfSales,"ProductName",RELATED(DIM_Product[ProductName]),"AccPeriodMonth",RELATED(DIM_AccountingPeriod[AccPeriodMonth]),"Companyname",RELATED(DIM_Company[CompanyName]),"TransactionType","CostOfSales","Amount",[CostOfSalesAmount]),

SELECTCOLUMNS(FACT_AdminCosts,"ProductName",RELATED(DIM_Product[ProductName]),"AccPeriodMonth",RELATED(DIM_AccountingPeriod[AccPeriodMonth]),"Companyname",RELATED(DIM_Company[CompanyName]),"TransactionType","AdminCosts","Amount",[AdminCosts]

))

     

    result:

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Pragati11 

 

I posted a detailed reply, but somehow it was marked as spam by the website.

Is it possible to "unspam" my reply?

  • Pragati11's avatar
    Pragati11
    Super User
    5 years ago

    Hi Anonymous ,

     

    I can't see your response.

    May be try posting it again or message admin.

     

    Thanks,

    Pragati

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      hi Pragati11 , v-lili6-msft 

       

      - I need to group by multiple DIM and FACT tables and then unpivot it using DAX

      - Unfortunately I cant use the GROUP BY and UNPIVOT functions in PowerQuery due my work being done in Visual Studio/Analysis Services as a Tabular model (compatibility limited to 2016 for our clients, otherwise I could use PowerQuery)


      - DIM_Tables:  DIM_Product: ID, ProductName ;

                              DIM_Company: ID, CompanyName ;

                              DIM_AccountingPeriod: ID, AccPeriodMonth

       

      - FACT Tables: FACT_Sales:            ProductID, CompanyID, AccPeriodMonthID, SalesAmount ; 

                              FACT_CostOfSales: ProductID, CompanyID, AccPeriodMonthID, CostOfSalesAmount ;

                              FACT_AdminCosts: ProductID, CompanyID, AccPeriodMonthID, AdminCosts

       

      - I need the following output:

       

      ProductName  |   AccPeriodMonth   |    Companyname   |    TransactionType               |   Amount

      1                      |     Jan20                   |        A                     |       Sales                              |     100
      2                      |     Jan20                   |        A                     |       CostOfSales                   |       50
      3                      |     Jan20                   |        A                     |       AdminCosts                   |       20
      1                      |     Feb20                  |        A                      |       Sales                              |     200
      2                      |     Feb20                  |        A                      |       CostOfSales                   |       70
      3                      |     Feb20                  |        A                      |      AdminCosts                    |       30
      1                      |     Jan20                   |        B                     |       Sales                              |     100
      2                      |     Jan20                   |        B                     |       CostOfSales                   |       50
      3                      |     Jan20                   |        B                     |       AdminCosts                   |       20
      1                      |     Feb20                  |        B                      |       Sales                              |     200
      2                      |     Feb20                  |        B                      |       CostOfSales                   |       70
      3                      |     Feb20                  |        B                      |      AdminCosts                    |       30

       

      I appreciate the help.

      Thank you in advance

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

        HI  Anonymous 

        You could try this formula:

        Table = 
UNION( 
SELECTCOLUMNS(FACT_Sales,"ProductName",RELATED(DIM_Product[ProductName]),"AccPeriodMonth",RELATED(DIM_AccountingPeriod[AccPeriodMonth]),"Companyname",RELATED(DIM_Company[CompanyName]),"TransactionType","Sales","Amount",[SalesAmount]),

SELECTCOLUMNS(FACT_CostOfSales,"ProductName",RELATED(DIM_Product[ProductName]),"AccPeriodMonth",RELATED(DIM_AccountingPeriod[AccPeriodMonth]),"Companyname",RELATED(DIM_Company[CompanyName]),"TransactionType","CostOfSales","Amount",[CostOfSalesAmount]),

SELECTCOLUMNS(FACT_AdminCosts,"ProductName",RELATED(DIM_Product[ProductName]),"AccPeriodMonth",RELATED(DIM_AccountingPeriod[AccPeriodMonth]),"Companyname",RELATED(DIM_Company[CompanyName]),"TransactionType","AdminCosts","Amount",[AdminCosts]

))

         

        result:

         

        and here is sample pbix file, please try it.

         

        Regards,

        Lin

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      this response keeps getting marked as spam
      Pragati11  v-lili6-msft 

      how do I contact administrator?