Forum Discussion

prajwal1's avatar
prajwal1
Frequent Visitor
2 years ago
Solved

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...
  • v-weiyan1-msft's avatar
    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.

v-weiyan1-msft's avatar
v-weiyan1-msft
Icon for Community Support rankCommunity 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.

prajwal1's avatar
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's avatar
    v-weiyan1-msft
    Icon for Community Support rankCommunity 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.