DAX measure correction

[prajwal1]

Hello,

I need your help to correct a dax expression.
I have a 'PIs info' table like below

PI	PI start date	PI end date	Previous PI code
PI 23-Q4	10-Sep-23	15-Dec-23	PI 23-Q4
PI 24-Q1	17-Dec-23	11-Mar-24	PI 23-Q4
PI 24-Q2	25-Mar-24	14-Jun-24	PI 24-Q1

I have another 'Tempo data' table like below

PI timing tag	PI code	Time worked hrs
PI 23-Q4	PI 23-Q4	4
PI 23-Q4	PI 24-Q1	2
PI 24-Q1	PI 23-Q4	3
PI 24-Q1	PI 24-Q1	5
PI 24-Q1	PI 24-Q2	4
PI 24-Q2	PI 24-Q2	6
PI 24-Q2	PI 24-Q1	3

When I select a PI in a slicer, then calculate the sum of Time worked hrs where the PI = PI timing tag and PI code in values of list of PI which are before the selected PI. FYI I don't have relationship between the tables (also cannot make it)
Example: PI 24-Q1 selected in slicer. The output I want 5+3=8. If PI 24-Q2 is selected, then it should 6+3=9.

I am using below DAX expression. But it is considering only one previous PI for the selected PI whereas it should consider all the previous PIs for the selected PI

Worked hrs for selected PI =

VAR Selected_PI = SELECTEDVALUE('PIs info'[PI])

VAR Previous_PI = SELECTEDVALUE('PIs info'[Previous PI code])

RETURN

    CALCULATE (

        SUM ( 'Tempo data'[timeworked] ),

        'Tempo data'[PI timing tag] = Selected_PI,

        'Tempo data'[pi_code] IN {Selected_PI, Previous_PI},

        'Tempo data'[weekstartdate] <= MAX('Tempo last updated date'[Logged till date])

    )

[v-weiyan1-msft]

Hi @prajwal1 ,

@some_bih Thank you, for your quick response and the solution provided.
I am not sure how your semantic model looks like.
Based on the sample and description you provided, perhaps you can try code as below to create measure.
My Sample:
PIs info:

Tempo data:

Worked hrs for selected PI = 
VAR Selected_PI = SELECTEDVALUE('PIs info'[PI])
VAR Previous_PI = MAXX(FILTER('PIs info','PIs info'[PI] = Selected_PI),'PIs info'[Previous PI code])
VAR Result = 
    CALCULATE(
        SUM('Tempo data'[Time worked hrs]),
        FILTER(
            'Tempo data',
            'Tempo data'[PI timing tag] = Selected_PI &&
            ('Tempo data'[PI code] = Selected_PI || 'Tempo data'[PI code] = Previous_PI)
        )
    )
RETURN
    IF(ISBLANK(Result), 0, Result)

In the Visualizations pane, select the Card icon and put the measure in the card visualization.
When you select "PI 24-Q2" in the slicer, Result is as below.

Is this the result you expect?

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.

[prajwal1]

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.

[v-weiyan1-msft · Accepted solution]

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.

[some_bih]

Hi @prajwal1 

According to measure definition, part 'Tempo data'[pi_code]  is related to IN for two different columns, which is not possible.

Rework your measure

Worked hrs for selected PI =
VAR Selected_PI = SELECTEDVALUE('PIs info'[PI])
VAR Previous_PI = SELECTEDVALUE('PIs info'[Previous PI code])
RETURN
CALCULATE (
SUM ( 'Tempo data'[timeworked] ),
'Tempo data'[PI timing tag] = Selected_PI,
'Tempo data'[pi_code] IN {Selected_PI, Previous_PI},
'Tempo data'[weekstartdate] <= MAX('Tempo last updated date'[Logged till date])
)