cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## 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 =
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.

3 REPLIES 3
Super User

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?

Owen Auger
Blog
Super User
• 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
GENERATESERIES ( 1, 12, 1 ),
"Row", [Value],
"Price", OrigPrice * POWER ( Ratio, [Value] - 1 )
)
``````

Regards

Owen Auger
Blog
Frequent Visitor

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?

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors