Measure all rows 'grouped' in 1 column if 1 row meets a condition

[ukozlows]

Hi,

First time posting.

I am working in PowerPivot and would like help with either a dax measure or a calculated column or simply a nifty way to add a conditional column using M in PowerQuery. (the only way i know how is to add a grouping and take the max sales, then ungroup with all data - but i have a few million rows and I don't know if this will be too heavy)

The situation is based on the customer groups shown under Cust and their respective sales shown under Sales, I want to flag the rows as Yes if any of the sales records are >0 for a given customer (so that I can measure all the visits if any sales were made).

Cust	Sales	Flag	Visits
Cust1	0	YES	5
Cust1	10	YES	15
Cust2	0	NO	0
Cust2	0	NO	5
Cust3	5	YES	5
Cust3	10	YES	5
Cust3	15	YES	10
measure visits
Cust1			20
Cust2			0
Cust3			20

[tamerj1 · Accepted solution]

Hi @ukozlows 

please try

Number of Visits :=
SUMX (
    VALUES ( 'Table'[Cust] ),
    CALCULATE ( IF ( SUM ( 'Table'[Sales] ) > 0, SUM ( 'Table'[Visits] ), 0 ) )
)

[ukozlows]

Thanks for the Measure!  But I am getting a blank result.

Maybe it is I am lookig for the total and not by customer.  (In this example would be 40 (20+20).)

Here is the error message I receive:

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Visits measure: =
IF (
    HASONEVALUE ( Data[Cust] ),
    IF ( SUM ( Data[Sales] ) <> 0, SUM ( Data[Visits] ), 0 )
)