Forum Discussion
DAX measure correction
Hi prajwal1 ,
Based on your additional instructions, please try the following steps:
My Sample:
PIs info:Tempo data:
1.You can create a calculated table.
Table 2 = DATATABLE ( "PI", STRING, "Order", INTEGER, { { "PI 23-Q3", 1 }, { "PI 23-Q4", 2 }, { "PI 24-Q1", 3 }, { "PI 24-Q2", 4 } } )2. Use the following code to create a measure.
Worked hrs for selected PI = VAR Selected_PI = SELECTEDVALUE('PIs info'[PI]) VAR PI_Order = MAXX(FILTER('Table 2','Table 2'[PI] = Selected_PI),'Table 2'[Order]) VAR Valid_PIs = CALCULATETABLE( VALUES('Table 2'[PI]), 'Table 2'[Order] <= PI_Order ) VAR Result = CALCULATE( SUM('Tempo data'[Time worked hrs]), FILTER( 'Tempo data', 'Tempo data'[PI timing tag] = Selected_PI && 'Tempo data'[PI code] IN Valid_PIs ) ) RETURN IF(ISBLANK(Result), 0, Result)When you select "PI 24-Q2" in the slicer, Result is as below.
Best Regards,
Yulia Yan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-weiyan1-msft . Thanks for your response. But in your logic it will always consider two PIs only. not more than that. What will happen if we have two previous PIs.?
For example the tables structure of Tempo data is like below
If I select PI 24-Q2 then it should display 6+3+1 = 10. Because it has two previous PIs.
Community SupportHi prajwal1 ,
Based on your additional instructions, please try the following steps:
My Sample:
PIs info:
Tempo data:
1.You can create a calculated table.
Table 2 =
DATATABLE (
"PI", STRING,
"Order", INTEGER,
{
{ "PI 23-Q3", 1 },
{ "PI 23-Q4", 2 },
{ "PI 24-Q1", 3 },
{ "PI 24-Q2", 4 }
}
)
2. Use the following code to create a measure.
Worked hrs for selected PI =
VAR Selected_PI = SELECTEDVALUE('PIs info'[PI])
VAR PI_Order = MAXX(FILTER('Table 2','Table 2'[PI] = Selected_PI),'Table 2'[Order])
VAR Valid_PIs =
CALCULATETABLE(
VALUES('Table 2'[PI]),
'Table 2'[Order] <= PI_Order
)
VAR Result =
CALCULATE(
SUM('Tempo data'[Time worked hrs]),
FILTER(
'Tempo data',
'Tempo data'[PI timing tag] = Selected_PI &&
'Tempo data'[PI code] IN Valid_PIs
)
)
RETURN
IF(ISBLANK(Result), 0, Result)
When you select "PI 24-Q2" in the slicer, Result is as below.
Best Regards,
Yulia Yan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.