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
 

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
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

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

forrester

New Member
Joined
Apr 19, 2018
Messages
7


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

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

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
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)
 

gazpage

Active Member
Joined
Apr 17, 2015
Messages
393
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
 

forrester

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

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

Active Member
Joined
Apr 17, 2015
Messages
393
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:

Forum statistics

Threads
1,085,419
Messages
5,383,550
Members
401,836
Latest member
Bweston07

Some videos you may like

This Week's Hot Topics

Top