cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ITManuel
Helper V
Helper V

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 2
ITManuel
Helper V
Helper V

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

Hei_aQing
Helper I
Helper I

Kick it up to notch

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors