Help with dax formula

[Syndicate_Admin]

Hello community!

I have this formula.

Copy and paste formula and paste image below.

INVESTMENT MEUR LAST FORECAST for TABLE =
var _lastdate = CALCULATE(MAX('AFO & SAP BW_Situación Económica IS'[DataKey]),ALL('AFO & SAP BW_Situación Económica IS'))
var _lastdate2 = CALCULATE(MAX('AFO & SAP BW_Presupuestación y Planificación IS'[DataKey]),ALL('AFO & SAP BW_Presupuestación Y Planificación IS'))
return
IF(SELECTEDVALUE('AFO Informe Global'[PROYECTO EN SAP Sí/No])="No",SELECTEDVALUE('AFO Informe Global'[Coste Final Estimado]),IF(calculate(SELECTEDVALUE('AFO & SAP BW_Situación Económica IS'[Coste final estimado SE])/1000000,'AFO & SAP BW_Situación Económica IS'[Grupo SE]="TOTAL",'AFO & SAP BW_Situación Económica IS'[DataKey]= _lastdate)=BLANK(),calculate(SELECTEDVALUE('AFO & SAP BW_Presupuestación Y Planificación IS'[Coste final estimado PP])/1000000,'AFO & SAP BW_Presupuestación Y Planificación IS'[DataKey]= _lastdate2) ,calculate(SELECTEDVALUE('AFO & SAP BW_Situación Económica IS'[Coste final estimado SE])/1000000,'AFO & SAP BW_Situación Económica IS'[Grupo SE]="TOTAL",'AFO & SAP BW_Situación Económica IS'[DataKey]= _lastdate)))

The problem is in the part that I paint in red or shade in yellow in the image (it's the same). When I filter by project (this is a field found in any of the tables involved in the Formula), it paints the same value for everyone. And if I don't select any, it leaves it blank. What measure can I use instead of SELECTEDVALUE so this doesn't happen?

[v-xulin-mstf]

Hi @Syndicate_Admin,

Maybe you need delete the function 'ALL'.

Best Regards,
Link

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

[Syndicate_Admin]

Thank you for your response.

No, it's not solved by removing the ALL. If I take it off, it has exactly the same behavior.

I don't think I'm doing that SELECTEDVALUE formula thing right. In the same .pbix I have another similar problem.

I have a "Has One Filter? • ISCROSSFILTERED('AFO Global Report'[Project Code])" so that if I segment by "Project Code" I will return data in some visuals.

If I select a project,

But if I select two, it leaves those objects blank.

The formula for Event Description is: IF([Has One Filter?],SELECTEDVALUE('AFO Global Report'[Last Quality Event Description],BLANK()))

And for Event Date: IF([Has One Filter?],SELECTEDVALUE('AFO Global Report'[Last Quality Event Date]),BLANK())

Always using my measure "Has One Filter?" (which is actually a CROSS), to make if I select one or two in slicer I will return data, and if I don't leave it blank.

Let's see if you can help me!

[v-xulin-mstf]

Hi @Syndicate_Admin,

Oh, I got it! I got the wrong direction before.😝

Please refer the definition of the functions:

https://docs.microsoft.com/en-us/dax/hasonefilter-function-dax

Returns TRUE when the number of directly filtered values on columnName is one; otherwise returns FALSE.

If you choose two slicer options, it will return Blank().

For your first issue:

Please refer:

https://docs.microsoft.com/en-us/dax/selectedvalue-function

Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.

So, what is your expected output for the measure?

Best Regards,
Link

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

[Syndicate_Admin]

Thanks in advance.

What I expect from the measure is that if I filter by two Projects, I give two results here:

Z event for one

Event X for another

(e.g.)

How could I do that? I have to touch the formula: "Have You One Filter? ISCROSSFILTERED('AFO Global Report'[Project Code])"; or I have to touch the formula: "Event Description is: IF([Has One Filter?],SELECTEDVALUE('AFO Global Report'[Last Quality Event Description],BLANK()))"

I don't know if what's wrong is the Blank() or that I don't have "alternateResult".

[v-xulin-mstf]

Hi @Syndicate_Admin,

Your table needs to have an index or serial number column to distinguish each 'Event', if not, please add an index column.

Try measure as:

Measure=
IF(
     ISFILTERED('AFO Global Report'[Project Code]),
     CALCULATED(
         MAX('AFO Global Report'[Last Quality Event Description]),
         FILTER(
            'AFO Global Report'[INDEX]=MAX('AFO Global Report'[INDEX])
         )),
     BLANK()
)

Best Regards,
Link

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

[Syndicate_Admin]

good!

Thank you for your reply, but I'm afraid in this case the measure is not correct.

The measure would be like that anyway, wouldn't it?

Measure=
IF(
     ISFILTERED('AFO Global Report'[Project Code]),
     CALCULATED(
         MAX('AFO Global Report'[Last Quality Event Description]),
         FILTER(
            'AFO Global Report',MAX('AFO Global Report'[INDEX])
         )),
     BLANK()
)

[v-xulin-mstf]

Hi @Syndicate_Admin,

Ah, sorry, I made a mistake.

You are right.

Anyway, does it work well?

Best Regards,
Link

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

[Syndicate_Admin]

Hello!

Thank you, as usual.

In this case it is not good for me because it shows me project value with the highest "index". And what I want is for you to show me a value for each project selected in the multiple segmentation selection.

[v-xulin-mstf]

Hi @Syndicate_Admin

Sorry, could you provide your pbix after removing sensitive information?

I will try it in my environment.

Best Regards,
Link

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