topic Measure to return a field based on two measures comparison in DAX Commands and Tips

Measure to return a field based on two measures comparison

AndreGiovannini — Fri, 05 May 2023 13:05:05 GMT

Hi,

I have a measure named 'Total Cost' that is based on (SUM('Table1'[Qty]) * SUM('Table2'[Cost]).
This 'Total Cost' is break by Item, Origin and Destination in a table resulting in:

Item | Origin | Destination | Total Cost
123 | BRA | BEL | 100
123 | ARG | BEL | 120

312 | BRA | COL | 300
(...)

I've created a 'Min Total Cost' measure to point out the best source cost:

CALCULATE(
MINX('Table2',[Total Cost]),
ALLSELECTED('Table2[Origin],'Table2'[Item-Origin])
)

This measure is working fine, so added to above:
Item | Origin | Destination | Total Cost | Min Cost
123 | BRA | BEL | 100 | 100
123 | ARG | BEL | 120 | 100

Now I want to point out what is the best source in each of the lines.
Item | Origin | Destination | Total Cost | Min Cost | Best Origin
123 | BRA | BEL | 100 | 100 | BRA
123 | ARG | BEL | 120 | 100 | BRA

How can I create the measure to point the 'Best Origin'?

Re: Measure to return a field based on two measures comparison

eliasayyy — Fri, 05 May 2023 14:00:35 GMT

hello please follow

first you can shorten your measure for minx with

Lowest price = MINX( ALLSELECTED('Table'[Origin]), [Sum cost] )

second you need to use topn

origin best = TOPN( 1,ALLSELECTED('Table'[Origin]),[Sum cost],ASC)

Re: Measure to return a field based on two measures comparison

AndreGiovannini — Fri, 05 May 2023 15:12:05 GMT

Hi eliasayyy ,

The image you sent is exactly what I need, but I could not replicate it in my model.

There is no error flag on measure, but once add it to a visual the following msg appears:

"MdxScript(Model) (75,1) Calculation error in measure 'SUP Measures'[Best Source]: A table of multiple values was supplied where a singular value was expected."

I believe that this is related to fields I'm aggregating in ALLSELECTED. Tried some other selections but couldnt solve it.

Any thougts of what I'm missing?

Re: Measure to return a field based on two measures comparison

eliasayyy — Fri, 05 May 2023 15:33:12 GMT

Can you send me the measure you used

Re: Measure to return a field based on two measures comparison

AndreGiovannini — Fri, 05 May 2023 18:21:16 GMT

Sure, here is it:

Best Source =
TOPN(1,
ALLSELECTED('DIM Avail Org'[Avail Org Code])
,[Total Landed]
,ASC)

I've been playing around with fields on ALLSELECTED, but couldnt solve it yet.

EDIT:
There is a Joiner table affecting this measure. Added the 'ALLEXCEPT' and measure do no break anymore. However I could not identify which exceptions should I use yet.

Best Source =
CALCULATE(
TOPN(1,
ALLSELECTED(JNR[Avail Org])
,[Total Landed]
,ASC)
,ALLEXCEPT(JNR,JNR[Avail Org]
))

Re: Measure to return a field based on two measures comparison

eliasayyy — Fri, 05 May 2023 18:10:24 GMT

Is there a relationship between table 1 and table 2?

Re: Measure to return a field based on two measures comparison

AndreGiovannini — Mon, 08 May 2023 20:17:19 GMT

Hi eliasayyy

Flagging your solution as accepted. Was able to replicated it in a different data model.

Thank you.