Need help with TREATAS

[ITManuel]

Hi all,

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

Table _T3:

ProjectID    Date          AVGPMBH

20010	03.05.2021	1,19331742
20010	04.05.2021	1,19331742
20010	05.05.2021	1,19331742
20010	06.05.2021	1,19331742
20010	07.05.2021	1,19331742
20010	08.05.2021	1,19331742
20010	09.05.2021	1,19331742
20010	10.05.2021	1,19331742
20010	11.05.2021	1,19331742
20010	12.05.2021	1,19331742
20010	13.05.2021	1,19331742
20010	14.05.2021	1,19331742
20010	15.05.2021	1,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

[ITManuel]

Hi Community,

any help here please?

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

ProjectID	Date	AVGPMBH
20010	03.05.2021	1,19331742
20010	03.05.2021	1,19331742
20010	03.05.2021	1,19331742
20010	03.05.2021	1,19331742
20010	03.05.2021	1,19331742
20010	03.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

[Hei_aQing]

Kick it up to notch