topic Re: Using a measure in DAX in DAX Commands and Tips

Using a measure in DAX

DominicHill — Mon, 02 Oct 2023 05:13:38 GMT

Hi Folks,

I have bamboozled myself - I can normally work out most things but this is evading me.

This code works;

PriceTable =
ADDCOLUMNS(
GENERATESERIES(1, 12, 1),
"Row", [Value],
"Price",
IF(
[Value] = 1,
[Orig Price],
SWITCH(
[Value],
2, [Orig Price] * 0.80,
3, [Orig Price] * 0.64,
4, [Orig Price] * 0.51,
5, [Orig Price] * 0.41,
6, [Orig Price] * 0.33,
7, [Orig Price] * 0.26,
8, [Orig Price] * 0.21,
9, [Orig Price] * 0.17,
10, [Orig Price] * 0.13,
11, [Orig Price] * 0.11,
12, [Orig Price] * 0.09
)
)
)

But the problem is that [Orig Price] is a variable and will contain a value from 250 to 3500. The [Orig Price] is a DAX as follows;

Orig Price = IF(DISTINCTCOUNT('Customer_Port_to_Port_Volumes'[Region to Region]) * 250 > 3499, 3500, DISTINCTCOUNT('Customer_Port_to_Port_Volumes'[Region to Region]) * 250) - again I know this works as I can see the values changing.

I appreciate the DAX can not be used as a measure, but I can't find a way forward.

All ideas gratefully received!

Re: Using a measure in DAX

OwenAuger — Mon, 02 Oct 2023 08:33:20 GMT

Hi DominicHill

Can you clarify what the issue is 🙂 ? i.e. what result are you currently getting vs what you expect?
In what context are you evaluating the PriceTable expression? Is it one part of a measure or something else?
Regardless of the definition of [Origin Price], its value will not change in any part of the PriceTable expression. In other words, [Origin Price] will be effectively constant for the purpose of the PriceTable expression (unless you want some other behaviour).
The table expression itself looks it should execute fine. I would just recommend simplifying to:

PriceTable = VAR OrigPrice = [Origin Price] VAR Ratio = 0.8 RETURN ADDCOLUMNS ( GENERATESERIES ( 1, 12, 1 ), "Row", [Value], "Price", OrigPrice * POWER ( Ratio, [Value] - 1 ) )

Regards

Re: Using a measure in DAX

DominicHill — Thu, 19 Oct 2023 12:26:22 GMT

Hi Owen,

Sorry I haven't responded before - I have been away.

The code from me generates

Which if I put into a matrix shows

The problem is that the first value (the 3,500) should be based on a calculated measure

This 'orig price' will change depending on other selections. Then Row 1 of the table should start at whatever is in the calculated measure 'Orig Price', and the remaining rows then calculate.

Does that make sense?

Re: Using a measure in DAX

OwenAuger — Sat, 21 Oct 2023 09:44:26 GMT

Hi DominicHill

Thanks for the clarification 🙂

If Price needs to update dynamically based on Orig Price (which is a measure), then Price must also be a measure.

This means that Price cannot be included in a table definition.

I would sugget creating a table containing the row numbers, then define the measure Price based on the max value of Row and Orig Price.

I have attached a small example.

Row table (could be defined in Power Query as well):

Row = VAR NumRows = 12 RETURN SELECTCOLUMNS ( GENERATESERIES ( 1, NumRows ), "Row", [Value] )

Price measure

Price = VAR Ratio = 0.8 VAR OrigPrice = [Orig Price] VAR CurrentRow = MAX ( 'Row'[Row] ) VAR Result = OrigPrice * POWER ( Ratio, CurrentRow - 1 ) RETURN Result

Table visual

Will that work for what you're doing?