Forum Discussion
Calculation at line level in PBI using direct query
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!
2 Replies
- amitchandak
Super User
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))) 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!
