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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ShNBl84
Helper II
Helper II

Use string value from table as a formula

We are trying to calculate the average price of an item over several months. The total price is the ingredient price plus the labor price. The labor price could change monthly.

We want to make a measure that shows the average price based on the selected months.

An example of a pricing formula is:

May 2019: (Ingredient price / .975) + 10

 

Instead of writing a new measure for every item and every month, we are experimenting with putting a DAX formula into Excel and inserting it as a measure. For example:

ItemDateFormula
Product 1May 2019=(SELECTEDVALUE([Ingredient price]) / .975) +10
Product 1June 2019=(SELECTEDVALUE([Ingredient price]) / .975) +11

 

Is there a way to pull a string value (such as those under Formula) and use them as formulas in Power BI?

Do you have a better solution?

 

Thanks.

1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

Hi @ShNBl84 ,

Unfortunately, it is impossible to use a string value as a formula to use directly in Power BI. Maybe you could try the Tabular Editor 3rd party tool as natelpeterson suggested.

I think you could import all the data and create formulas to implement in Desktop. I create a sample to explain what I mean.

 

Measure =
VAR a =
    SELECTEDVALUE ( 'Table'[Ingredient price] )
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Table'[Month] ) = 9, ( a / 0.975 ) + 10,
        MAX ( 'Table'[Month] ) = 10, ( a / 0.975 ) + 11,
        SUM ( 'Table'[Ingredient price] )
    )

 

3.PNG

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-xuding-msft
Community Support
Community Support

Hi @ShNBl84 ,

Unfortunately, it is impossible to use a string value as a formula to use directly in Power BI. Maybe you could try the Tabular Editor 3rd party tool as natelpeterson suggested.

I think you could import all the data and create formulas to implement in Desktop. I create a sample to explain what I mean.

 

Measure =
VAR a =
    SELECTEDVALUE ( 'Table'[Ingredient price] )
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Table'[Month] ) = 9, ( a / 0.975 ) + 10,
        MAX ( 'Table'[Month] ) = 10, ( a / 0.975 ) + 11,
        SUM ( 'Table'[Ingredient price] )
    )

 

3.PNG

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@ShNBl84  - The Tabular Editor 3rd party tool could be useful. There are scripts and strategies to easily create many measures. However, I'd recommend reviewing what you're trying to accomplish here.

 

An alternative is to have a table of labor prices, which could be consumed by the measure - then you're not maintaining many measures, but rather one which pulls in the relevant values. Perhaps we can further explore what's needed here?

 

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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