Sum all sales by ID if status has reached X

[Anonymous]

Guys, I need a measure. Hope someone can help 🙂  

I've got a question regarding how to sum all rows of a column by an ID based on the following requirement: 

- Only sum the points of IDs if that ID has one (or more) rows containing "some_text" in a third column (in my example the text "Finished").

Here's an example: 

In this example, the measure should return: 35 (since the sum of points of all IDs with one or more Status="Finished" is 35).

Thanks for answers. 

[baghdadi62]

If you have only one "Finished" in your table:

CALCULATE(SUM(Data[Points]), FILTER(Data, Data[ID] = LOOKUPVALUE(Data[ID], Data[Status], "Finished")))

[Anonymous]

Thanks for replying. 

To clarify, there are many "finished" in the column. It is a massive table with millions of rows. Would this still work?

[baghdadi62]

No, becaues lookupvalue works only with one value to find.

Can we use new column before having measure or it is forcible to have a measure only?

[Anonymous]

It's connected through Direct Query, but I can have a calculated column in the Data-tab in my solution (thats no problem). 

[baghdadi62 · Accepted solution]

First create a table for finished status IDs:

Finished_Table = FILTER(Data, Data[Status]="Finished")

Then create a new column to calculate sum(points) of IDs in that table:

Sum_of_Finished = CALCULATE(SUM(Data[Points]), FILTER(Data, Data[ID] = Finished_Table[ID]))

[Anonymous]

I feel confident this is the right solution, however I'm getting the error "exceeding max row limit of 1000000" 😕 
This is because I'm using Direct Query. 

I'll mark your solution as the right answer for the record, but if you have any suggestion on how I can make this solution work with the DQ limitations I'd be most grateful. 

Best. PJ

[baghdadi62]

And a measure for show result in a Card:

Measure_3 = SUMX(ADDCOLUMNS(VALUES(Data[ID]), "x", CALCULATE(COUNTROWS(Data))),
            IF([x]=3, CALCULATE(SUM(Data[Points]))))

[baghdadi62]

OK.

and here is a measure for you:

Measure_2 = 
Var x = countrows(Data)
return
if(x=3, sum(Data[Points]))

It can be used in a Matrix with IDs.