Forum Discussion

Nihed's avatar
Nihed
Icon for Helper III rankHelper III
4 years ago

Mesure dax

Hello,

Someone can give me an idea how I can do this mesure in Dax please 

 

Mesure=

IF Steps = " 1-Planned/Not Started " then 0

IF Steps = "2 - TORs received/under review” then AVERAGE(TORs Completed - TORs received)

IF Steps = " 3-Open for Competition " then AVERAGE(Closes on - Posted on)

IF Steps = "4- Technical Evaluations" then AVERAGE(Technical Evaluation Completed - Closes On)

IF Steps = "5 - Financial Negotiations" then AVERAGE(Negotiation Completed - Technical Evaluation Completed)

IF Steps = " 6-Procurement Board " then AVERAGE(PB Date - Negotiation Completed)

IF Steps = "7-Legal Review" then AVERAGE(Reviewed by LEG - PB Date)

IF 'PPM'[Step] = "8-Signature" AND 'SUI'[Reference] = "RFQ*" then AVERAGE(STEP DURATION = 'SUI'[Date Signature] - 'SUI'[Reception de la demande])

IF 'PPM'[Step] = "9-Finalised" then STEP DURATION = SUM(each STEP DURATION from TORs to Signature)

 

 

Thank you

9 Replies

  • ValtteriN's avatar
    ValtteriN
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi,

    The function that you are looking for is SWITCH combined with TRUE. 

    My example data:

    Example measure:

    Switch_example =
    var _value = MAX('Switch example'[Selection]) return
    SWITCH(TRUE(),
    _value="1-Planned/Not Started",1,
    _value="2 - TORs received/under review",AVERAGE('Matrix example'[Value]),
    _value ="3-Open for Competition",3
    //Use the same structure of (_value ="what the value should match",what values is returned if the match is okay) for further steps
    )
    End result:

     

    Things to note:
    If you need to do addition and substraction calculations combined with average you should do them separately before AVERAGE e.g. in a calculated column.

    Hopefully this helps to solve your issue and if it does consider accepting this as a solution!
    • Nihed's avatar
      Nihed
      Icon for Helper III rankHelper III
      4 years ago

      thank you for your return
      what you have done is calculate each FI separately while
      what I sent is a single measure that contains all this calculation I send you the data table in a pbix to explain my need

      the need is to calculate this KPI:

       

      The example data in pbix in this link :

      https://we.tl/t-ODV6VPsZhm 

       

      Thanks again

      • ValtteriN's avatar
        ValtteriN
        Icon for Community Champion rankCommunity Champion
        4 years ago

        Hi,

        Unfortunately due to information security reasons I can't view your example data. I am a bit perplexed as to why the switch measure doesn't work. The logic behind the measure is that if the source column's value is something e.g. "1-Planned/Not Started" then the measure returns value after the next comma. So in essence it functions like this:

        var example = max(here you should refrer to the source fied e.g. [Steps]) return

        Switch(true(),
        if variable value =  "1-Planned/Not Started" then return this
        if variable value = "2 - TORs received/under review" then return this

        ... and so on)

        I am not sure this clarifies the logic or if I have misunderstood something.

    • Nihed's avatar
      Nihed
      Icon for Helper III rankHelper III
      4 years ago

      Hello,

       

      I send you the link of the example I am working you can see the data in the pbix  https://we.tl/t-VMz3BFCyNS 

      my need is to calculate the following KPIs if you can help me:

       

       

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Nihed 

    Firstly, you need to change the type of date value from text to Date type.

    then, there are something need to be checked,

    (1) what's the SUI table? This table doesn't exist in the sample file.

    (2) In your rules, please see the picture bellow, the logic of 1st part is same and I've wrote the dax expression for this part, please check whether the result is right for you. Kindly note: change the type to date then test the measure. 

    Measure = 
var _value = MIN(PPM[Steps])
return SWITCH(TRUE(),
_value="1 - Planned/Not Started",0,
_value="2 - TORs received/under review",AVERAGEX(ALL(PPM),PPM[TORs completed]-PPM[TORs received]),
_value="3 - Open for Competition",AVERAGEX(ALL(PPM),PPM[Closes on]-PPM[Posted on])
)

    And the logic of 2nd part, I'm not sure what the redlined sentence means. Could you explain them? 

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Nihed 

    I notice that this post is inactive, may I know if you have solved this question with help? If you have solved the question, you can accept the answer helpful as the solution or share you method and accept it as solution, thanks for your contribution to improve Power BI.

    If you need more help, please let me know.

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.