# DAX - Using a Calculated Column in a Measure



## forrester (Apr 19, 2018)

Hello,

I am to figure out if it is possible to use a calculated column in a measure.

The Calculated Column counts number of accounts per user: #ofAccounts  =IF(ISBLANK(CALCULATE(COUNTA(Accounts[Contract Id]),FILTER(ALL(Accounts),Accounts[Contract Id]=Contracts[Contract Id]))),1,(CALCULATE(COUNTA(Accounts[Contract Id]),FILTER(ALL(Accounts),Accounts[Contract Id]=Contracts[Contract Id]))))

If the #ofAccounts  = 1 I want to use measure A, if the # of accounts is >1 I want to use measure B.

The problem I am running into is in writing the measure is I can not reference the calculated column I created to count the number of accounts.
Attempt: :=IF(data[#ofAccounts ]>1, B, A)  ---I am unable to reference #ofAccounts  currently.

Thanks


----------



## gazpage (Apr 19, 2018)

This isn’t to do with calculated columns. You can’t use a ‘naked’ column in a measure like this. Your measure basically says if {entire column} > 1, which’s makes no sense. You need to put a function around the column. 

What are you trying to achieve in the end?


----------



## forrester (Apr 19, 2018)

I have two pricing measures written A and B. If a customer has more than 1 account I want to use measure B, If a customer only has 1 account I want to use measure A.


Customer#ofAccounts
Measure to Use12
B21A31A44B51A62B

<tbody>

</tbody>
Thanks


----------



## forrester (Apr 19, 2018)

Customer
Account
#ofAccounts
Measure to use
1
1
2
B
1
2
2
B
2
1
1
A
3
1
1
A
4
1
4
B
4
2
4
B
4
3
4
B
4
4
4
B
5
1
1
A
6
1
2
B
6
2
2
B


<tbody>

</tbody>
Better example of the table


----------



## forrester (Apr 19, 2018)

Better example of the table


CustomerAccount#ofAccountsMeasure to Use112---use measure B122---use measure B211---use measure A311---use measure A414---use measure B424---use measure B434---use measure B444---use measure B511---use measure A612---use measure B622---use measure B

<tbody>

</tbody>


----------



## gazpage (Apr 20, 2018)

Ok. I don't really understand why you are using a calculated column, seems like that should just be a measure. If you stick to that method, try:


```
IF( SELECTEDVALUE ( data[[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ofAccounts"]#ofAccounts[/URL]] ) >1, B, A)
```


----------



## gazpage (Apr 20, 2018)

Ok,  I quickly pulled in the Customer and Account columns from your table above.

I then wrote the following two measures.


```
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ofAccounts]#ofAccounts[/URL]  = 
SUMX (
    VALUES ( Data[Customer] ),
    CALCULATE ( 
        COUNTROWS ( Data ),
        ALL ( Data[Account] )
    )
)
```


```
MeasureToUse = 
IF (
    [[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ofAccounts]#ofAccounts[/URL] ] > 1,
    "B",
    "A"
)
```

Dropping those in a table visualisation returned exactly the table that you said 'better version...' I note that it is not actually necessary to add the #ofAccounts  column to the table to get the A / B result.

Obviously in the above it just returns text A or B, and also it returns a total of 11 accounts and therefore B, which you could remove by using an isfiltered


----------



## forrester (Apr 20, 2018)

The table I'm actually using is a lot more complex than the example I gave so the first measure won't work, but thanks for the input. I'll work on just doing the #ofAccounts  as a measure and not a calculated column so that my if statement which matches yours in that example will work.


----------



## forrester (Apr 20, 2018)

So I got the first measure to work for counting the accounts per customer, but the second measure needs to be adjusted to have A or B applied based on the account level not the customer level.

Ex. customer 1 shows 2 in the measure. However, when running the second measure it treats them both as a 1 instead of a 2 and is applying A instead of B. (This is the reason I was originally using a calculated column, because it would fix the count at 2 in the account level)


----------



## gazpage (Apr 21, 2018)

Can you post your measures. Does your first measure work in the exact output table you are using? Ie does it produce your eacample table earlier in the thread. 

Also help if you can explain the relationship between the two tables containing these two columns.


----------



## forrester (Apr 19, 2018)

Hello,

I am to figure out if it is possible to use a calculated column in a measure.

The Calculated Column counts number of accounts per user: #ofAccounts  =IF(ISBLANK(CALCULATE(COUNTA(Accounts[Contract Id]),FILTER(ALL(Accounts),Accounts[Contract Id]=Contracts[Contract Id]))),1,(CALCULATE(COUNTA(Accounts[Contract Id]),FILTER(ALL(Accounts),Accounts[Contract Id]=Contracts[Contract Id]))))

If the #ofAccounts  = 1 I want to use measure A, if the # of accounts is >1 I want to use measure B.

The problem I am running into is in writing the measure is I can not reference the calculated column I created to count the number of accounts.
Attempt: :=IF(data[#ofAccounts ]>1, B, A)  ---I am unable to reference #ofAccounts  currently.

Thanks


----------



## forrester (Apr 23, 2018)

I was able to get everything working over the weekend.

For the first measure I got it to work using: #ofAccounts:=CALCULATE(DISTINCTCOUNT([Account]),ALL(Data[Account]))

Thanks again for the suggestion to due both calcs as measure. Lead me to a working solution.


----------

