Forum Discussion
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
| 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
2 Replies
- Hei_aQingHelper I
Kick it up to notch
- ITManuelResponsive Resident
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