DAX measure correction

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

2 years ago

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.

DAX measure correction.pbix59 KB

4 Replies

some_bih
Community Champion
2 years ago
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])
)
v-weiyan1-msft
Community Support
2 years ago
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.
DAX measure correction.pbix56 KB
- prajwal1
  Frequent Visitor
  2 years ago
  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
    Community Support
    2 years ago
    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.
    DAX measure correction.pbix59 KB

Forum Discussion

DAX measure correction

4 Replies

Related content

Dax Formula correction help

Dax correction

DAX Measure

POWER BI DAX MEASURE

Correct the DAX measure closing count DAX measure