dax fixed count

[Forrester]

Hello,

I am trying to get a fixed count for accounts under a customer so that I can apply 1 of 2 billing models (measure A for customers with 1 account, measure B for customers with >1 account).

Table			Results I'm Trying to achieve
Customer	Account		#ofAccounts	Measure to Use
1	1		2	---use measure B
1	2		2	---use measure B
2	1		1	---use measure A
3	1		1	---use meaure A
4	1		4	---use meaure B
4	2		4	---use meaure B
4	3		4	---use meaure B
4	4		4	---use meaure B
5	1		1	---use meaure A
6	1		2	---use meaure B
6	2		2	---use meaure B

Problems:

Calculated Column Method:

If i create the #ofAccounts as a calculated column I am struggling to pull it into the billing measure:

Example: Billing:=IF([#ofAccounts]>1,[Measure B],[Measure A]) --Problem I cant refence {#ofAccounts) because its not reference customer

Measure Method:

#ofAccounts:=CALCULATE(DISTINCTCOUNT(Data[AccountId]))

This works if just looking at the measure, if I filter on customer 1 it returns a value of 2. However, when i try to apply the billing measure --Billing:=IF([#ofAccounts]>1,[Measure B],[Measure A])

it just reads off the account column and treats everything as a 1.

Thanks

[v-yulgu-msft · Accepted solution]

Hi @Forrester,

Problems:

Calculated Column Method:

If i create the #ofAccounts as a calculated column I am struggling to pull it into the billing measure:

Example: Billing:=IF([#ofAccounts]>1,[Measure B],[Measure A]) --Problem I cant refence {#ofAccounts) because its not reference customer 

Maybe you could try this formula:

Billing=IF(MAX([#ofAccounts])>1,[Measure B],[Measure A])

Best regards,

Yuliana Gu

[Greg_Deckler]

Need source data sample/example. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490