Dax Equavalent of Vlookup

Yrstruly · March 10, 2021, 2:12pm

Scenario is: Marketing request ad hoc reports on marketing and sales. Report should include SKUs/ Lines by category and by month. I am seraching for products by item in my powerbi report, see: https://drive.google.com/file/d/1jL4vNdBxAi0LPigy3iaQ-iGFzHiMPaBA/view?usp=sharing

Because the above report do not include app category. I also have to search for the relevant caategories in this report

see: https://drive.google.com/file/d/1t_6HxBXHgqcJc_M2OsWamWCstJQMrCkb/view?usp=sharing

Process is manually and repetitive to get an output like the following in excell. See: https://drive.google.com/file/d/1M2uarnUy6-534WKrZ1-YSxDDEmYEHNXg/view?usp=sharing

I have to mannually copare the master data vendor sheet(see: https://drive.google.com/file/d/1PyQ6tBTyaDuj-sml2TZjUkY6Ujttri8K/view?usp=sharing), with my current powerbi/excel files.

I am matching the app category with the second link’s data(see above). To match the items according to the category it falls within my mcommerce/app.

See Sales Value and Sales Volume in the RB sheet, output should look like that.

Then i need to calculate the Sales Volume Share and Sales Value share(see RB sheet for example).

(1)I would like to combine the two reports that sits in powerbi(see first two links). To give me the desired output in the RB file, see: https://drive.google.com/file/d/1M2uarnUy6-534WKrZ1-YSxDDEmYEHNXg/view?usp=sharing

(2) I would like to get the the volume shares like in the last two examples in the RB file, without having to do it manually everytime.

*Please assist, how do i match the records like in excel’s vlookup?

*Do i bring in a caculated column or calculated table for the Volumes Shares output?

The following sql brings in relevant tables:

App Filter Categories

select a.label as ‘App Category’
, b.label as ‘Menu’
, c.product_uid as ‘Product UID’
from app_menu_filters a
left join app_menu_filters b on b.parent_uid=a.uid
left join app_menu_filter_products c on b.uid=c.app_menu_filter_uid
where a.status=‘A’ and a.level=1
order by 1,2,3;

Sales and Product Data

SELECT date(o.created_datetime) as Date, od.product_uid as ‘Product UID’,
p.manufacturer Manufacturer, p.bmc BMC, p.brand Brand, od.label as SKUs, p.selling_unit as ‘Unit of Measure’,
ROUND(if(od.amended_quantity is not null, od.amended_quantity, od.quantity)) as ‘Units Sold’,
ROUND((if(od.amended_quantity IS NOT NULL, od.amended_quantity, od.quantity))*p.content,2) as ‘Sales Volume’,
ROUND((if(od.amended_quantity is not null, od.amended_quantity, od.quantity))*od.price,2) as ‘Sales Value’
FROM order_detail od
left join order o on od.order_uid=o.uid
left join product p on od.product_uid=p.uid
where o.status in (‘D’,1,2,3,4,5)
and not od.label =‘Plastic Bag’
and date(o.created_datetime) >= ‘2020-04-01’

Basket Level Data

BrianJ · March 10, 2021, 3:29pm

@Yrstruly,

Thanks very much for the detailed explanation and the PBIX files - makes it much easier to provide quality solutions. I’ve got to run to a series of meetings now, so will just provide the short answer in the hope that it will give you what you need to solve this, and if not will be glad to work through the specifics with you.

The equivalent of VLOOKUP in Power BI is LOOKUPVALUE and it works much the same way. Here’s a full rundown on it from the eDNA knowledgebase:

https://info.enterprisedna.co/dax-function-guide/lookupvalue/

However, there may be a better option - while we typically don’t think of it in this way, merge in Power Query performs the same function as LOOKUPVALUE. If you do a left outer join and then expand to bring in Sales Volume Shares, that will just create that column in the table during the load process, and then Volume Shares will be accessible just like any other column currently in the dataset for further calculations,.

I hope this is helpful.

Brian

Yrstruly · March 11, 2021, 10:07am

Sample data: https://drive.google.com/file/d/1-p_F5hGztBd2LL0_mI7Kv8uyDgdTmOLa/view?usp=sharing

Yrstruly · March 11, 2021, 7:02pm

Thank you for the feedback.

If you look at the volume data in that excel file. The volume share is calculated by unit of measure x units sold. Thats created every time the report needs to be done. I want that repetitive process eliminated

ankit · March 17, 2021, 10:33am

Hi @Yrstruly

I am having difficulty understanding your requirement. Based on what you have shared, I can see there are two visuals as below and you wan’t to combine them

Brand and filters on SKU(Products)
App Category with filters on App Category

Please clarify below points and share the sample PBIX files to look into the issue.

Is the issue because App Category and Products/Brands in different tables or is it something else?
What is the formula for Volume Share?

Thanks
Ankit J

EnterpriseDNA · March 18, 2021, 1:37am

Hi @Yrstruly, we’ve noticed that no response has been received from you since the 11th of March. We are waiting for the masked demo pbix file, answers to @Ankit’s questions above, and any other supporting links and details.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Yrstruly · March 18, 2021, 10:18am

Here is the sql i use to draw in the data in pbx file:

App Filter Categories

select a.label as ‘App Category’
, b.label as ‘Menu’
, c.product_uid as ‘Product UID’
from app_menu_filters a
left join app_menu_filters b on b.parent_uid=a.uid
left join app_menu_filter_products c on b.uid=c.app_menu_filter_uid
where a.status=‘A’ and a.level=1
order by 1,2,3;

Sales and Product Data

SELECT date(o.created_datetime) as Date, od.product_uid as ‘Product UID’,
p.manufacturer Manufacturer, p.bmc BMC, p.brand Brand, od.label as SKUs, p.selling_unit as ‘Unit of Measure’,
ROUND(if(od.amended_quantity is not null, od.amended_quantity, od.quantity)) as ‘Units Sold’,
ROUND((if(od.amended_quantity IS NOT NULL, od.amended_quantity, od.quantity))*p.content,2) as ‘Sales Volume’,
ROUND((if(od.amended_quantity is not null, od.amended_quantity, od.quantity))*od.price,2) as ‘Sales Value’
FROM order_detail od
left join order o on od.order_uid=o.uid
left join product p on od.product_uid=p.uid
where o.status in (‘D’,1,2,3,4,5)
and not od.label =‘Plastic Bag’
and date(o.created_datetime) >= ‘2020-04-01’

Basket Level Data

pbx

I would like a desired outpput such as https://drive.google.com/file/d/1M2uarnUy6-534WKrZ1-YSxDDEmYEHNXg/view?usp=sharing

https://drive.google.com/file/d/1dwPDuuOju7nbLyDVa5njvLObCqviJa3Z/view?usp=sharing

https://drive.google.com/file/d/1m47pBgUfTcbtLsVlELNc_EpoaTTVwX6N/view?usp=sharing
and
https://drive.google.com/file/d/1m47pBgUfTcbtLsVlELNc_EpoaTTVwX6N/view?usp=sharing

@ankit

Yrstruly · March 20, 2021, 9:25am

Here it is, Yes i want to combine it to give the output as in the excel examples? It is an unnecessary and tedious task to do it every time.
Pbx file is to big to attached. see link.
https://drive.google.com/file/d/1HCoYlPrhBQKuvWb-TpOBbDBEnv0BV0Sj/view?usp=sharing

Yrstruly · March 21, 2021, 10:10pm

Thank you for assisting. Yes, it should be easy. But it’s not.

See data: https://drive.google.com/file/d/1zqSbAvESHf5WUw4U-OKXjL_9E8NbJTfd/view?usp=sharing

I want to asnwer these questions: https://drive.google.com/file/d/1plg9Ocy2kJdM3sR2WpXY_uSiXRQbhUFN/view?usp=sharing

Yrstruly · March 22, 2021, 6:23am

Data in excel n DDL

Excel data

Yrstruly · March 22, 2021, 10:34am

@BrianJ please assist?

EnterpriseDNA · March 30, 2021, 12:44am

Hi @Yrstruly! We noticed that your inquiry was left unsolved for quite some time now. We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!