Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Kick it up to notch
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |