Forum Discussion

Forrester's avatar
Forrester
New Member
8 years ago
Solved

dax fixed count

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
CustomerAccount #ofAccountsMeasure to Use
11 2---use measure B
12 2---use measure B
21 1---use measure A
31 1---use meaure A
41 4---use meaure B
42 4---use meaure B
43 4---use meaure B
44 4---use meaure B
51 1---use meaure A
61 2---use meaure B
62 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's avatar
    v-yulgu-msft
    8 years ago

    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

2 Replies