topic Re: Measure give the value 95 or the next up in DAX Commands and Tips

Measure give the value 95 or the next up

MTrullàs — Fri, 01 Jul 2022 10:40:26 GMT

Hello,

I need a measure that reflects the value "Tran. Campa" when the value "P. Tran. Campa" is 95%. In the event that there is no 95% value in "P. Tran. Campa", then the measure should give the value "Tan. Campa" upper, as seen in the screenshots.

Thank you very much in advance,

Greetings

Re: Measure give the value 95 or the next up

johnt75 — Fri, 01 Jul 2022 13:05:21 GMT

I think this measure should do it

P95 Trans Campa = VAR summaryTable = TOPN ( 1, FILTER ( HIFA, [P. Trans Campa] >= 0.95 ), HIFA[Trans Campa], ASC ) RETURN SELECTCOLUMNS ( summaryTable, "@val", [Trans Campa] )

Re: Measure give the value 95 or the next up

MTrullàs — Wed, 06 Jul 2022 11:50:07 GMT

Hello john75,

Thank you very much for your replay, until today I couldn't check the measure. I think it's in the right way, but there is something about the format of P. Trans Campa that it gives always the first value.

I think, but I don't know exactly where is the problem. In this screen shot of two examples, you could see that the measure gives the first value, not the correct.

Thank you very much for your time!

Re: Measure give the value 95 or the next up

johnt75 — Wed, 06 Jul 2022 14:12:24 GMT

Try creating a calculated table, or run the query in DAX Studio,

FILTER ( HIFA, [P. Trans Campa] >= 0.95 )

to see what it returns.

Is it possible to share the PBIX file ?

Re: Measure give the value 95 or the next up

MTrullàs — Thu, 07 Jul 2022 05:11:45 GMT

Hello johnt75,

Thanks for your answer.

I'm new to the forum and I don't know how can I share the PBIX file.

Could you give me some tips?

Thanks again for your time!

Re: Measure give the value 95 or the next up

johnt75 — Thu, 07 Jul 2022 08:02:20 GMT

People usually use a file sharing service like Dropbox, or share directly from Google Drive or One Drive.

Re: Measure give the value 95 or the next up

MTrullàs — Fri, 08 Jul 2022 04:45:32 GMT

Hello johnt75,

Thanks very much for your tips.

Finally, I could build an example to share with you throughout google drive. This is the link: https://drive.google.com/drive/folders/1WcvmaWZ6qjvbZL7GvzSw0ura9GBXYL4s?usp=sharing

If you have any problem to access, please let me know.

So, I need a metric which reflects the value "Estancia Fabrica" or "Tiempo Total" when the value "P.Estancia Fabrica" or "P. Tiempo Total"is 95%. In case it is not 95%, then the measurement should give the higher value.I share two examples more about the pbix that I'm sharing.

Thanks for hearing me out.

Re: Measure give the value 95 or the next up

johnt75 — Fri, 08 Jul 2022 10:02:16 GMT

OK, I've found the problem with the original code, when using all of the columns in HIFA in the FILTER statement the calculation always produces 1. Try the below

P95 Tiempo Total = VAR summaryTable = TOPN ( 1, FILTER ( values(HIFA[Tiempo Total]), [P. Tiempo Total] >= 0.95 ), HIFA[Tiempo Total], ASC ) RETURN SELECTCOLUMNS ( summaryTable, "@val", [Tiempo Total] )

Re: Measure give the value 95 or the next up

MTrullàs — Fri, 08 Jul 2022 14:22:29 GMT

Thank you very much!

You are great!