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?:

       

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

        I see now. Like I mentioned in things to note, in these kind of cases I would create a calculated column for the substraction operation and then take The average from this calculated column. This is due to The fact that AVERAGE only accepts column as The value. For now I think this is the most straightforward approach.