Forum Discussion

DominicHill's avatar
DominicHill
Frequent Visitor
2 years ago

Using a measure in DAX

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!

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    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's avatar
      DominicHill
      Frequent Visitor
      2 years ago

      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?

       

       

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    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?