DAX - Using a Calculated Column in a Measure

forrester

New Member
Joined
Apr 19, 2018
Messages
7
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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?
 
Upvote 0
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 Use
12
B
21A
31A
44B
51A
62B

<tbody>
</tbody>

Thanks
 
Upvote 0


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
 
Upvote 0
Better example of the table

CustomerAccount#ofAccountsMeasure to Use
112---use measure B
122---use measure B
211---use measure A
311---use measure A
414---use measure B
424---use measure B
434---use measure B
444---use measure B
511---use measure A
612---use measure B
622---use measure B

<tbody>
</tbody>
 
Upvote 0
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:

Code:
IF( SELECTEDVALUE ( data[[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ofAccounts"]#ofAccounts[/URL]] ) >1, B, A)
 
Upvote 0
Ok, I quickly pulled in the Customer and Account columns from your table above.

I then wrote the following two measures.

Code:
[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] )
    )
)

Code:
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
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top