VALUES Function

[Anonymous]

Hello,

I have a metric to get the amount of average buying days of customers. I need the same metric for VIP customers. I have been trying to filter the VALUES function but it didn´t work.

Here it the basic metric that I want to adapt:

DIVIDE( SUMX( VALUES(Customer[Customer ID]), CALCULATE(DISTINCTCOUNT('Transaction'[Date]) ) ), [# Buyers] )

I have been trying this:

 DIVIDE( SUMX( CALCULATE(VALUES(Kunden[Kunden ID]), Customer[VIP_FLAG]=TRUE), CALCULATE(DISTINCTCOUNT('Transaction'[Date]) ) ), [# Buyers VIP] )

 We have in the customer table another column (VIP_FLAG) telling us if the customer is a VIP or no (boolean) .

Actually I just need to filter the VALUES function, just don´t know how. I have tryied all possible DAX functions that I know.

I don´t want to filter the visual as the metric should be use in a table sowing other KPIs.

Hope you can help me.

Thanks.

[PabloDeheza]

Hi there!

Try using the FILTER funcion instead of CALCULATE, so:

DIVIDE( SUMX( FILTER(VALUES(Kunden[Kunden ID]), Customer[VIP_FLAG]=TRUE), CALCULATE(DISTINCTCOUNT('Transaction'[Date]) ) ), [# Buyers VIP] )

SUMX() accepts a table as a first parameter so using calculate wont work there, can try using FILTER, which returns a table as a result.

hope it helps.

[Anonymous]

Already tried this before.

It doesn´t work.

"A single value for column "VIP_FLAG" in Table Customer cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result"

[PabloDeheza · Accepted solution]

Oh right. assuming Kunden[Kunden ID] is on the many side of the relationship you can try this as first parameter of the SUMX fuction.

 FILTER (
    SUMMARIZE (
        Kunden,
        Kunden[Kunden ID],
        Customer[VIP_FLAG]
    ),
    Customer[VIP_FLAG] = TRUE ()
)

[Anonymous]

Thanks a lot, it did work!!