cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Using measures in calculated columns

Hi all,

 

I have a table with the following columns,

 

Factory, productGroup, productSubgroup, week, total failed, total produced

 

 

I have created a measure that calculates the %defective (total fail/total produced) for any combination of the 4 attributes.

 

Now I want to create a calculated column (which I want to use for further calculations) which holds the value of the above measure but instead it recalculates the expression for each row. I want the calculated column to show one value of %defective based on the measure which in turn is calculated dynamically based on whatever fields I select (any combination of factory, productGroup, productSubgroup )

 

Essentially an aggregated %defective over weeks for any combination of Factory, productGroup, productSubgroup.

 

NOTE: This is slightly different than doing a summarize over weeks since when I would select say a particular factory its going to do an average of all %defective rather than doing the calculation from sractch. 

4 REPLIES 4
Moderator v-qiuyu-msft
Moderator

Re: Using measures in calculated columns

Hi @Pariksht_verma,

 

We can create a calculated column to reference a measure value like this: Column1= <measure name>. But you need to note the calculated column values are calculated based on table context so it's fixed. To do further calculation, you can use measure directly without creating additional calculated column. If you have some specific requirement, please share the sample data and expected results for our analysis.

 

Best Regards,
Qiuyun Yu

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
stevebi Frequent Visitor
Frequent Visitor

Re: Using measures in calculated columns

Did you find a solution to this? I'm facing a similar issue.

 

I have a measure that calculates the maximum value in a column on one table. In this case, it is finding the most recent month for which certain information was entered.

  • EOMI_LM = max('WInventory)'[Combined Date (Numeric)])

If I place the measure on a visualization page, it will show the current value for it. Currently that expression yields: 201702

 

Now I want to use the measure in a formula for a calculated column in another table. But it doesn't work. The wrong result will be shown. 

  • EOMILen_LM = sumx(filter(filter('WInventory','WInventory'[Part #]=zLKPIM[Product #]),'WInventory'[Combined Date (Numeric)]=EOMI_LM),'WInventory'[Pieces on Hand])

However, if I substitute the Measure's formula instead of its name, the calculation will work. 

  • EOMILen_LM = sumx(filter(filter('WInventory','WInventory'[Part #]=zLKPIM[Product #]),'WInventory'[Combined Date (Numeric)]=max('WInventory'[Combined Date (Numeric)])),'WInventory'[Pieces on Hand])

Or, if I substitute the static value it will also work. 

  • EOMILen_LM = sumx(filter(filter('WInventory','WInventory'[Part #]=zLKPIM[Product #]),'WInventory'[Combined Date (Numeric)]=201702),'WInventory'[Pieces on Hand])

So then why doesn't it work when I just reference the name? Why can't I use that measure like a variable in the formula for other calculated columns?

 

Steve

MilanRao06 Frequent Visitor
Frequent Visitor

Re: Using measures in calculated columns

I have a similar problem. Any response on this please?

andrewporter Frequent Visitor
Frequent Visitor

Re: Using measures in calculated columns

I want to create a calculated column (which I want to use for further calculations) which holds the value of the above measure but instead it recalculates the expression for each row

 

Calculated Columns are evaluated and calculated before the user even starts tinkering with Filters, Slicers, or anything like that. So, the misunderstanding is that Query and Filter Context do not apply to Calculated Columns on rows.  Thus, if you add a measure value to a calculated column, it will only abide by the Row Context and the calculated column value of a measure will be the same for every row in your table. 

 

You will need to shift your thinking a little, create a new Measure based on your already existing Measure in order to utilize filter and query contexts. Read the article a bunch, my brain hurt for a long time before DAX even started to make sense even a little bit.

 

Here is a snippet from MSDN:

 

For example, suppose you create this simple formula that sums the values in the Profit column of the Sales table: =SUM('Sales'[Profit]). If you use this formula in a calculated column within the Sales table, the results for the formula will be the same for the entire table, because the query context for the formula is always the entire data set of the Sales table. Your results will have profit for all regions, all products, all years, and so on.

 

MSDN Context in DAX Formulas

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 280 members 3,001 guests
Please welcome our newest community members: