Using a measure in DAX

[DominicHill]

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!

[OwenAuger]

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?

[OwenAuger]

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

[DominicHill]

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?