Forum Discussion

leon179's avatar
leon179
Frequent Visitor
4 years ago

Need help regarding DAX measure.

DEFINE 

VAR ParmPPO                         = @Parm_Parent_Project_Only
VAR Parm_Project_ID           = @Parm_Project_ID
VAR Parm_Company             = "KGPS"
VAR __Report2B                = {("PRJ0000001.0001","200i",100.00,50),
                                                  ("PRJ0000001.0001","200e",200.00,40),
                                                  ("PRJ0000001.0003","200f",100.00,50)
                                                 } 


 VAR __Report1A =
FILTER (
    ADDCOLUMNS (
        SUMMARIZECOLUMNS (
            'dimProject'[Project ID],
            factProjectFinancials[Category],
            KEEPFILTERS ( TREATAS ( { Parm_Company }, dimCompany[Company UC] ) ),
            KEEPFILTERS ( TREATAS ( { "Posted Project Transactions" },factProjectFinancials[Table Source])),
            "Total_Actual_Cost", [Project Posted Total Actual Cost]
        ),
        "Forecast_Total_Cost_Amount", 0
    ),
    (
        Parm_Project_ID = "%" /* forces the DAX to run for ALL projects */
            || (
                ParmPPO = "0"
                    && CONTAINSSTRING ( 'dimProject'[Project ID], Parm_Project_ID )
            ) /* works as a LIKE without wildcards */
            || (
                ParmPPO = "1"
                    && PATHCONTAINS ( Parm_Project_ID, 'dimProject'[Project ID] )
            ) /* retuns the | delimited list entered (or single value)... i.e. parent project PRJ0000015 */
    )
)


VAR __Report1B =
SELECTCOLUMNS (
    __Report1A,
    "Project ID", [Project ID],
    "Category", [Category],
    "Total Forecast Cost", [Forecast_Total_Cost_Amount],
    "Total Actual Cost", [Total_Actual_Cost]
)

VAR Combine1_2 = 
    UNION(__Report1B,__Report2B)


VAR Grouped_1_2 =
GROUPBY (
    Combine1_2,
    [Project ID],
    "Total Forecast Cost", SUMX ( CURRENTGROUP (), [Total Forecast Cost] ),
    "Total Actual Cost", SUMX ( CURRENTGROUP (), [Total Actual Cost] )
)    

EVALUATE Grouped_1_2

What is the final total for "PRJ0000001.0001" on "Total Forecast Cost"?

3 Replies

  • Is this from a textbook?  You are supposed to use DAX Studio to connect to your sample dataset?

  • leon179's avatar
    leon179
    Frequent Visitor

    I don't have a sample dataset, I only have a question and I want to know the answer.

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User

      We can give you an answer if you provide the following information:

       

      @Parm_Parent_Project_Only
      @Parm_Project_ID
      dimProject

      factProjectFinancials
      dimCompany
      [Project Posted Total Actual Cost]