Calculation at line level in PBI using direct query

[DataAnalystSH]

Hello all wise heads! 😎

I am struggling with calculation at line level using direct query to dataset.

I need to calculate how many "Orders" produce only "Services (TDS)". 

I have following table in my report with this structure of data, contains "Order", "Order Line Number", "Category":

Order         Order LineNr  Category

SO34	1	TDG
SO34	2	TDG
SO34	3	TDG
SO34	4	TDG
SO78	1	TDS
SO78	2	TDS
SO87	1	TDG
SO87	2	TDG
SO87	3	TDS
SO87	4	TDS
SO87	5	TDG
SO87	6	TDS

I need to calculate measure, which shows me only "Order" which contain ONLY "TDS"

It means that if the one "Order" with multiple "Order Line Nr." has in column "Category" "TDG" and "TDS", I am not counting it.

If if the one "Order" with multiple "Order Line Nr." has in column "Category" only "TDS", no combination or "TDG", then I am counting it.

I try to use function EARLIER in measure but it doesn´t works (probably there is limitation of using direct query).

Could you please help me with that?🤔

Thank you very much!

[DataAnalystSH]

Hi @amitchandak 

I tried your measure and it shows me an error 

I also try something like this:

= IF (MAXX (FILTER ( data,data[Sales Voucher Number] = EARLIER ( data[Sales Voucher Number] ) ),

( data[POG Level 1])) = "TDS%",

"No",

"Yes")

it also doesn´t work, because it assigned only YES to all records..

but thank you for your answer!

[amitchandak]

@DataAnalystSH , try a new measure

new measure =
var _cnt = countx(allselected(Table), [Order] = max(Table[Order]) && Table[Category] <> "TDS")
return
calculate(count(Table[Order LineNr]), filter(Table, isblank(_cnt)))