DAX 'AND' Operator error in power bi measure

1
votes

I have a simple DAX formula that i want to use to distinct count the number of customers who have met a certain criteria. I have a table that defines these criteria i.e Tiers I am doing this on power bi and this is the measure that i am using;

MTierB = 
var y = SUM('Brand Tiers'[TierB]),
var x = SUM('Brand Tiers'[TierA])                                                                           
return CALCULATE(DISTINCTCOUNT('Sales By Customers'[CUSTOMER_StoreCode]), 

KEEPFILTERS(FILTER('Sales By Customers', 'Sales By Customers'[Total Sales in 

MSU] >= y AND <=x )))

I am getting a syntax error. The AND operator is underlined in red and i dont understand why? Please help out

2
powerbidax

2 Answers

0
votes

In DAX, AND is a function where AND(a,b) means "a and b". (Reference)

You can use && as a logical conjunction instead though. That is, AND(a,b) == a && b

In your case, you need the FILTER part of your measure to look like this

FILTER('Sales By Customers',
       'Sales By Customers'[Total Sales in MSU] >= y &&
       'Sales By Customers'[Total Sales in MSU] <= x)

or like this

FILTER('Sales By Customers',
       AND('Sales By Customers'[Total Sales in MSU] >= y,
           'Sales By Customers'[Total Sales in MSU] <= x))
0
votes

Kevin,

Also, no commas between the variable statements. 

MTierB =
VAR y =    SUM ( 'Brand Tiers'[TierB] ) // no comma
VAR x =    SUM ( 'Brand Tiers'[TierA] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Sales By Customers'[CUSTOMER_StoreCode] ),
        KEEPFILTERS (
            FILTER (
                'Sales By Customers',
                'Sales By Customers'[Total Sales in MSU] >= y
                    && 'Sales By Customers'[Total Sales in MSU] <= x
            )
        )
    )