Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Converting 3 steps table to only one table using CALCULATETABLE function

Hi, I am new to DAX and I have an assignment which requires me to simplify all 3 steps table to only one table using CALCULATETABLE function in DAX.


Been trying this for almost 2 weeks now, still can't seem to get the idea.
Does anyone know how I can simplify these steps to one table only using CALCULATETABLE function?

Thank you

 

Table 1:

 

forecasting_uoa_transactions_step1 = 
    FILTER (  
        SUMMARIZE ( 
            uoa_transactions_history, 
            uoa_transactions_history[Date], 
            uoa_transactions_history[Country], 
            "UoA", SUM(uoa_transactions_history[Total Changes])
        ),  
        DATEDIFF(uoa_transactions_history[Date],TODAY(),MONTH)<variable_uoa_per_cust_window[Variable_UoA_per_Cust_Window Value]+1
    )

 

Table 2:

 

forecasting_uoa_transactions_step2 = 
    SUMMARIZE ( 
        forecasting_uoa_transactions_step1, 
        forecasting_uoa_transactions_step1[Country],
        "UoA Per Customer Average", AVERAGE(forecasting_uoa_transactions_step1[UoA_per_Customer])
    )

 

Table 3: 

 

forecasting_uoa_transactions_step3 = 
    SELECTCOLUMNS ( 
        customer_transactions_forecast,
        "Date", customer_transactions_forecast[YearMonth],
        "Country", customer_transactions_forecast[Country],
        "Customers", customer_transactions_forecast[Total Headcount]
    )

 



3 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support

    Hi Anonymous ,

    Could you pls share the sample data for uoa_transactions_history ,variable_uoa_per_cust_window,customer_transactions_forecast  that I could test if if could? And remember to remove confidential data.

     

     

    Best Regards

    Lucien

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi v-luwang-msft thank you for your time! Send me your email so i could share the file with you, thank you

    • v-luwang-msft's avatar
      v-luwang-msft
      Community Support

      Hi Anonymous ,

      You could share by a public link.

       

       

      Best Regards

      Lucien