Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
DominicHill
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 =
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 3
OwenAuger
Super User
Super User

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]
    )

OwenAuger_0-1697881292253.png

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

OwenAuger_1-1697881381845.png

Will that work for what you're doing?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi Owen,

 

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

The code from me generates

DominicHill_0-1697718096448.png

Which if I put into a matrix shows

DominicHill_1-1697718129601.png

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

 

DominicHill_2-1697718187091.png

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?

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors