Vlookup in Dax

[Yrstruly2021]

I have the following excel data: https://drive.google.com/file/d/1Dy7ZrSE3PHNkRpetXNxIpL0Pyr24TF4q/view?usp=sharing

To get the output in column C. I match it, using vlookup with columns A, B. What is the equalivent in dax/powerbi please?

@amitchandak

[Yrstruly2021]

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?

[Yrstruly2021]

I use this sql to get relevant tables etc

# 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

[amitchandak]

@Yrstruly2021 , Actually logic is not very clear.

We have lookupvalue and earlier are very powerful tools to work within the table for new column

https://www.sqlbi.com/articles/introducing-lookupvalue/

earlier

https://www.youtube.com/watch?v=62_H3Bxu9ZM

https://blog.enterprisedna.co/how-to-use-the-earlier-function-in-power-bi-a-dax-tutorial/

Please provide logic to suggest a better formula

[Yrstruly2021]

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