How to modify my PBI approach to achieve faster way ?

[admin11]

Hi All

Every month i will be given a list of custmer vendor code to enter into below script :-

_Flag High Risk Acc = IF ( or

(

AR[Customer/Vendor Code] IN { "A10130", "I10058", "CU1001", "AU10191", "A10188", "E10085", "FU1008", "E10085", "IU1054", "R10051", "M10123","J10027","J10027","M10143","N10058","V10020","R10002","P10035","V10011","P10119","B10010","H10020","1000","N10039","K10067","N10063","pu0120","s10022","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx","Vxxxxxx" },

AR[AR Invoice Number] IN { 122097,122652 }

), 1, 0)

So that it will generate a report for high risk customer which they are solw payment.

I like to make this task to account click to enter , so i don't need to edit the script myself , which is very time consumming.

Can some one share it there any faster way ?

Paul

[TheoC]

Hi @admin11 

I suggest you make a simple Excel spreadsheet that contains High Risk vendor codes. Use the Excel spreadsheet as a Data Source that connects to your Vendor Master File data source by a relationship with Vendor ID.

Relationships will look like this and you can use the VendorID from High Risk Vendors table for filtering out High Risk Vendors:

From there, the only change you need to make is in the Excel spreadsheet rather than manipulating codes.  In fact, you will be able to get rid of measure / column given that the Excel spreadsheet will act as your source for identifying high risk vendors.

Once you have done that, you can use the new data source as the basis of filtering High Risk vendors by simply dragging the "Vendor ID" from the new data source table in Power BI and then adding whatever other data you want from your already existing data in Power BI 🙂

Outputs will look like this (prior to your filtering etc).

Hope this helps.

Theo

[admin11]

@TheoC 

Thank you very much for sharing detail step.

For my this project , it does not relate to sales table . ( Base on your image you indicate sales table ) , So i assume your sales table refer to AR Table.

Also for my application , i don't have vendor master file table , For my AR Table , it have a list of customer , some are high risk , so are not high risk. So i can assume AR table is what you refer to venfor master file,

So i only need to create a High Risk Vendor table. right ?and link this table with my AR Table,

Paul

[TheoC · Accepted solution]

Hi @admin11 

Yes, you are correct.  The tables I created are just dummy tables to provide an example.

All you need to do is create an Excel File that has a list of your customers that are high risk.  Then, from there, you can link it to your Accounts Receivable table.  using the ID 🙂

[admin11]

@TheoC 

For my excel table , it is like below is enough?

High_Risk

121314

123245

Paul

[TheoC]

Hi @admin11 Paul,

Yes, correct. Just a name for the column (e.g. VendorID) and the relevant Vendor IDs that are High Risk. From there, link it as a relationship to your AR table.

Theo 😀

[admin11]

@TheoC 

Thank you for your sharing. Appreciate very much.