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 Com...
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

        The problem that I can’t average because fields like following :

        for example I have AVERAGE(TORs Completed - TORs received)  how can i do it?: