Forum Discussion

ITManuel's avatar
ITManuel
Responsive Resident
4 years ago

Need help with TREATAS

Hi all,

 

i have the following table which is calculated in a measure by the following code. 

 

Table _T3:

ProjectID    Date          AVGPMBH

2001003.05.20211,19331742
2001004.05.20211,19331742
2001005.05.20211,19331742
2001006.05.20211,19331742
2001007.05.20211,19331742
2001008.05.20211,19331742
2001009.05.20211,19331742
2001010.05.20211,19331742
2001011.05.20211,19331742
2001012.05.20211,19331742
2001013.05.20211,19331742
2001014.05.20211,19331742
2001015.05.20211,19331742

 

 

    VAR _T1 =
        ADDCOLUMNS (
            ADDCOLUMNS (
                FILTER (
                    SUMMARIZE ( EPS, EPS[Project ID], EPS[Step], EPS[StepID], EPS[Date] ),
                    ( EPS[Project ID] = "20010"
                        || EPS[Project ID] = "21002"
                        || EPS[Project ID] = "21003" )
                        && (
                            (
                                [StepID] = "Des"
                                    && NOT ( CONTAINSSTRING ( [Step], "End" ) )
                                        || [StepID] = "PT1"
                            )
                        )
                ),
                "DateDiff",
                    VAR _MinDate =
                        CALCULATE (
                            MIN ( EPS[Date] ),
                            ALLEXCEPT ( EPS, EPS[Project ID] ),
                            EPS[Step] = "DesStart1"
                        )
                    VAR _MaxDate =
                        CALCULATE (
                            MAX ( EPS[Date] ),
                            ALLEXCEPT ( EPS, EPS[Project ID] ),
                            EPS[Step] = "PT1"
                        )
                    VAR _Result =
                        DATEDIFF ( _MinDate, _MaxDate, WEEK )
                    RETURN
                        _Result,
                "PMBH", CALCULATE ( SUM ( EPS[PM BH] ) ),
                "EndDate",
                    CALCULATE (
                        MAX ( EPS[Date] ),
                        ALLEXCEPT ( EPS, EPS[Project ID] ),
                        EPS[StepID] = "PT1"
                    )
            ),
            "AVGPMBH", DIVIDE ( [PMBH], [DateDiff] )
        )
    VAR _T2 =
        SELECTCOLUMNS (
            FILTER ( _T1, [StepID] = "Des" ),
            "Project ID", [Project ID],
            "AVGPMBH", [AVGPMBH],
            "StartDate", [Date],
            "EndDate", [EndDate]
        )
    VAR _T3 =
        SELECTCOLUMNS (
            GENERATE ( _T2, DATESBETWEEN ( 'Date'[Date], [StartDate], [EndDate] ) ),
            "ProjectID", [Project ID],
            "Date", [Date],
            "AVGPMBH", [AVGPMBH]
        )
RETURN
    _T3

 

 

Since I'm using ADDCOLUMNS and GENERATE I think the resulting table _T3 has lost its data lineage (the values are not turning up correctly when used in a visual) which I would like to change.

I tried,

 

    VAR _T4 =
        TREATAS ( _T3, EPS[Project ID], 'Date'[Date], EPS[PM BH] )
RETURN
    _T4

 

however its returns a blank table. 

 

If I understand correctly, the data lineage of column [AVGPMBH] would not need to be changed since it represents values and is not used for filtering, but its part of _T3 consequently I need to specify a data lineage in the TREATAS function.

 

I would need the exact table _T3 with datalineage for [ProjectID] of EPS[Project ID] and [Date] of 'Date'[Date].

 

How can I achieve this?

 

Thanks in advance

 

 

2 Replies

  • ITManuel's avatar
    ITManuel
    Responsive Resident

    Hi Community,

     

    any help here please?

     

    To summarize quickly the initial question, I have the following table calculated in a measure.

     

    ProjectIDDateAVGPMBH
    2001003.05.20211,19331742
    2001003.05.20211,19331742
    2001003.05.20211,19331742
    2001003.05.20211,19331742
    2001003.05.20211,19331742
    2001003.05.2021 1,19331742

     

    In order to overcome the issue i described for now, rather to calculated the table for usage in a measure, I have created a calculated table and created physical relationships of the calculated table with Project[ProjectID] and Date[Date], so the table is filtered correctly by the 'Project' and 'Date' table. 

     

    I would rather prefer to only calulcate the table in a measure and use the [AVGPMBH] column in a following SUMX function, but do this I need to ensure the table calculated in the measure is filtered by the 'Project' and 'Date' table. 

     

    Any help here please?

     

    Best regards