DAX - Using a Calculated Column in a Measure

[forrester]

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]

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]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]#ofAccounts
[/TD]
[TD]Measure to Use[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2
[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

Thanks

 

[forrester]

[TABLE="width: 500"]
<tbody>[TR]
[TD]

Customer
[/TD]
[TD]Account
[/TD]
[TD]#ofAccounts
[/TD]
[TD]Measure to use
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]4
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]B
[/TD]
[/TR]
</tbody>[/TABLE]

Better example of the table

 

[forrester]

Better example of the table

Customer	Account
	---use measure B
	---use measure B
	---use measure A
	---use measure A
	---use measure B
	---use measure B
	---use measure B
	---use measure B
	---use measure A
	---use measure B
	---use measure B

<tbody>
[TD="width: 104"]#ofAccounts[/TD]
[TD="width: 20"][/TD]
[TD="width: 247"]Measure to Use[/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]

[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2[/TD]

[TD="class: xl65"]2[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]1[/TD]

[TD="class: xl65"]3[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]1[/TD]

[TD="class: xl65"]4[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]4[/TD]

[TD="class: xl65"]4[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]4[/TD]

[TD="class: xl65"]4[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]4[/TD]

[TD="class: xl65"]4[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]4[/TD]

[TD="class: xl65"]5[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]1[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2[/TD]

[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2[/TD]

</tbody>

 

[gazpage]

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]

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]

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]

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]

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.