Calculate unique table from existing

[Adak]

Hi,

 It should be easy, but I have never done similar thing and can't find similar example. Main thing is that I have a table of orders, and only with purchase order VendorID value is present, later operations with that order lacks VendorID. Business wants to have information about other operations and slice it by Vendor. So my thought is to make a calculated table with distinct Orders and calculated VendorID for Order and make relationship with Orders table. That should solve business need, but I'm not sure how to calculate this table, or if calculated table would be the best approach.

I have a table like this:

Resulting calculated table should be like this: 

 Tried some DAX formulas with SUMMARIZE, but don't know how to filter out only one value ignoring blank ones (in reality Vendor ID is Text field, not number)

Would be great if someone would share DAX formula i should use to achieve this, or a better method to calculate this.

[Vvelarde · Accepted solution]

Hi @Adak

Specifically for your case you can solve trying this:

Tabla = SUMMARIZE(FILTER('Table','Table'[OperationType]="In"),'Table'[OrderID],'Table'[VendorID])

[Adak]

Thanks @Vvelarde

Looks so simple then looking at this 🙂

[Greg_Deckler]

I think you would have better luck creating another query against your data source that filtered for Vendor ID and did a distinct on Order ID. Then you would have the table you want and be able to relate it back to your Order table.