Not able to use Calculate on fields

[Anonymous]

Hi!

Totally newbie question...

I retrive my data from an SQL-source and from here I am only able to use DAX function Calculate on a measure I created myself with Divide and Sum. When I use Divide and Sum in a measure I am able to choose from all fields. Why am I not able to choose from all fields when using Calculate as well?

[Nathaniel_C · Accepted solution]

Hi @Anonymous  @tamerj1 ,
The reason this does not show the columns is that when your write a measure, you must reduce the column down to one value.  One way to do this is to preceed the column name with(MAX(). It is not really the Max of the column because on each row when the measure does the work, there is only one value.  See below:

Match = IF(MAX(table[col1]= MAX(table[col2]), "YES")

So, when you add the MAX() to the calculation, you will see all your available columns.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Anonymous]

Terrific! Many thanks @tamerj1  and @Nathaniel_C. I now get the idea and everything works as expected. 🙂

[tamerj1]

Hi @Anonymous 

In order to use measures inside CALCULATE you need to use the extended syntax and filter a table. Please refer to the following link for the explanation. 
https://youtu.be/jYaSd565GgE

[Anonymous]

Thank you for answering, Nathaniel.

I do not know if I am allowed to post any .pbix-files due to organizational policy.

Even though the picture is not the best option I will try it that way since you might get an Idea of my problem.

I want to be able to do the following measure:

TESTMEASURE = CALCULATE('Orderrader'[KundTotalRad],SAMEPERIODLASTYEAR('Orderrader'[FakturaBokföringsdatum]

But Calculate will only let me chose my previous own created measure 'Orderrader'[Provision %], not 'Orderrader'[KundTotalRad]. However, when using SUM, DIVIDE or SAMEPERIODLASTYEAR every field is selectable, like 'Orderrader'[KundTotalRad].

[tamerj1 · Accepted solution]

@Anonymous 

Yes because the first argument of CALCULATE has to be an expression that yields a scalar value (an aggregation of a column if you would, such as MAX, MIN, AVERAGE, SUM, etc. or just another measure. The second argument is the filter or the CALCULATE modifier which have to tables. So you can write for example 

CALCULATE ( SUM ( 'Orderrader'[KundTotalRad] ), SAMEPERIODLASYEAR ( 'Orderrader'[FakturaBokföringsdatum] ) )

[Nathaniel_C · Accepted solution]

Hi @Anonymous  @tamerj1 ,
The reason this does not show the columns is that when your write a measure, you must reduce the column down to one value.  One way to do this is to preceed the column name with(MAX(). It is not really the Max of the column because on each row when the measure does the work, there is only one value.  See below:

Match = IF(MAX(table[col1]= MAX(table[col2]), "YES")

So, when you add the MAX() to the calculation, you will see all your available columns.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @Anonymous 

If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you. 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Nathaniel