topic Re: Mesure dax in DAX Commands and Tips

Mesure dax

Nihed — Mon, 06 Dec 2021 14:31:01 GMT

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

Re: Mesure dax

ValtteriN — Tue, 07 Dec 2021 08:49:19 GMT

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!

Re: Mesure dax

Nihed — Tue, 07 Dec 2021 09:49:42 GMT

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

Re: Mesure dax

ValtteriN — Tue, 07 Dec 2021 10:46:56 GMT

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.

Re: Mesure dax

Nihed — Tue, 07 Dec 2021 11:44:09 GMT

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

Re: Mesure dax

ValtteriN — Tue, 07 Dec 2021 11:54:45 GMT

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.

Re: Mesure dax

smpa01 — Tue, 07 Dec 2021 15:48:09 GMT

Nihed provide sample data and expected output.

Re: Mesure dax

Nihed — Tue, 07 Dec 2021 15:53:47 GMT

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:

Re: Mesure dax

v-xiaotang — Thu, 09 Dec 2021 07:10:46 GMT

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.

Re: Mesure dax

v-xiaotang — Wed, 22 Dec 2021 05:14:59 GMT

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.