0

Follow Up :

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 (text - will explain)

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

Used the tier measure as the answer below (thank you)

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

VAR CustCount = COUNT ( Customers[CustomerID] )

RETURN

IF (

ISBLANK ( Limit ), "Select a value",

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

)

Now I need to aggregate 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.

Is that possible ?

enter image description here

Improve this question

1 Answer 1

Reset to default
0

You can capture the limit using SELECTEDVALUE and then compare.

Tier =
VAR Limit = SELECTEDVALUE ( CustomerLimit[CustomerLimit] )
VAR CustCount = COUNT ( Customers[CustomerID] )
RETURN
    IF (
        ISBLANK ( Limit ), "Select a value",
        IF ( CustCount > Limit, "Good", "Bad" )
    )
Improve this answer
3
  • It does not work, it still shows "Select a value". can it might be that the Customerlimit values are not recognized as a value ?
    – Jojo
    Commented Aug 12, 2021 at 15:47
  • This should work unless you created a calculated column instead of a measure. A calculated column cannot be dynamically responsive to filters since it is only calculated once each time you load or refresh your model, so you must use a measure for this.
    – Alexis Olson
    Commented Aug 12, 2021 at 15:52
  • thank you it worked. On a followup, do you know how can i use this Tier in a 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.
    – Jojo
    Commented Aug 12, 2021 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.