CODEWITHSUNDEEP
powerbidaxpowerquerypowerbi-desktop
Jojo
Jojo

Reputation: 15

error in Calculate : A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed

I have these two tables that are mutually exclusive (not connected in any way) .

The first table has date , number of customers on the dayDISTINCTCOUNT(sales[user_name]), total sales , tier (calculated measure below )

The second table is CustomerLimit which is basically consecutive numbers between 1 and 100.

Tier = VAR Limit = SELECTEDVALUE ( CustomerLimit[CustomerLimit] )

VAR CustCount = COUNT ( sales[user_name] )

RETURN

IF (

ISBLANK ( Limit ), "Select a value",

IF ( CustCount > Limit, "Good", "Bad" )

)

Now I need to aggregate YTD the total amount of customers by Tier. I used calculate(DISTINCTCOUNT(sales[user_name]),Tier = "Good") .

It give me an error of : A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Can someone please help me with how can I adjust this calculate function to aggregate them?

Thank you

enter image description here

Upvotes: 0

Views: 4538

Answers (1)

Marcus
Marcus

Reputation: 4015

You cannot use a measure value in a predicate within a calculate filter.

But you can create a filter using FILTER to filter your table by your measure value. Your measure must be on the form of :

Good Customers =
CALCULATE (
    DISTINCTCOUNT ( 'sales'[user_name] ) ,
    FILTER ( 
        'sales' ,
        [Tier] = "Good"
    )
)

Upvotes: 0

Related Questions