Filter depending on other values in same column

[Anonymous]

Hello!

I have a dataset looking like this:

Date	ID	Category	Value
01012019|	45	Quantity	439579
01012019|	45	Leadtime
01012019|	46	Quantity	32142
01012019|	46	Leadtime	10
01022019|	45	Quantity	35436
01022019|	45	Leadtime	2

I only want to count the value on quantity if the leadtime is not blank. 
Do you have any idea how I should filter?

Quantity tot of selected = 
CALCULATE(
    SUM(Data[Value]),
    FILTER(ALLSELECTED(Data), Data[Category]="Quantity")
)

This is how far I've come. 

Thank you for your help!

[v-xiaotang · Accepted solution]

Hi @Anonymous 

use this measure:

Measure = 
var tab =selectcolumns( filter(summarize('Table', 'Table'[Date], 'Table'[ID], "t", calculate(sum('Table'[Value]), 'Table'[Category] = "Leadtime")), [t]<>blank()), "date",'Table'[Date],"id", 'Table'[ID])
return
calculate( sum('Table'[Value]), FILTER(ALL('Table'),'Table'[Category]="Quantity"),INTERSECT(tab, selectcolumns('Table', "date",'Table'[Date],"id", 'Table'[ID])))

result:

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@Anonymous 

you can try this

select quantity column and pivot column in PQ

then create a measure

CALCULATE(
    SUM(Data[Value]),
    FILTER(Data, leadtime<>"")
)