0

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

Jojo
  • 15
  • 1
  • 5
  • https://stackoverflow.com/questions/43672351/a-function-calculate-has-been-used-in-a-true-false-expression-that-is-used-as-a/43696470#43696470 – RADO Aug 13 '21 at 19:14
  • I have checked this before , and as far as I understood ( sorry I am still new) the measure that the user created gives one static value and he/she has something in the source table that matches. The variable "tier" though has not a cell in the source table to match against, plus it gives 2 values dependent on how many customers bought something that day. What tier calculates is based on the number I select on CustomerLimit (consecutive numbers ), it checks on that day if the customers are more than the number I selected, then it says good, if they are less then it says bad – Jojo Aug 13 '21 at 20:09

1 Answers1

0

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"
    )
)
Marcus
  • 3,346
  • 1
  • 5
  • 23